package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.xmy.cultivate.entity.BookOrder;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.BookStudent;
import com.xmy.cultivate.entity.request.BookClassTypeCountRs;
import com.xmy.cultivate.entity.request.BookSchoolCountRs;
import com.xmy.cultivate.entity.request.BookSchoolRs;
import org.apache.ibatis.annotations.*;
import org.springframework.web.bind.annotation.RequestParam;

import java.io.IOException;
import java.util.List;

/**
 * <p>
 * 教材订购管理 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2025-01-04
 */
public interface BookOrderMapper extends BaseMapper<BookOrder> {

    @Select({"<script>",
            "<when test='subscribeType == 1 '>",
                "WITH subscribe_student_tb AS (",
                    "SELECT * FROM (",
                        "SELECT ",
                        "t01.id,",
                        "t01.student_name,",
                        "t01.student_id,",
                        "t01.class_type_id,",
                        "t01.year_class_id,",
                        "t01.school_id,",
                        "t02.subjects_id,",
                        "lowest_shutnum,",
                        "IFNULL( t02.right_num, 0 ) AS right_num,",
                        "IFNULL( t02.amend_num, 0 ) AS amend_num,",
                        "CASE WHEN lowest_shutnum &gt; 0 THEN ROUND( IFNULL( right_num / lowest_shutnum, 0 ) * 100, 2 ) ELSE 0 END AS rate ",
                        "FROM ",
                        "<if test='subjectsId == 2'>",
                            "math_db.per_student_account AS t01",
                        "</if>",
                        "<if test='subjectsId == 3'>",
                            "physics_db.per_student_account AS t01",
                        "</if>",
                        "INNER JOIN (",
                        "SELECT * FROM  ",
                        "<if test='subjectsId == 2'>",
                            "math_db.cha_plan",
                        "</if>",
                        "<if test='subjectsId == 3'>",
                            "physics_db.cha_plan",
                        "</if>",
                        "WHERE",
                        "year_part = ${yearPart}",
                        "AND quarter_num = ${quarterNum}",
                        "AND task_category_id = 1 ",
                        "AND deleted = 0 ",
                        "AND lesson_num = ${beforeLessonNum}",
                        "AND is_first = 1 ",
                    ") AS t02 ON t01.student_id = t02.student_id ",
                    "AND t01.class_type_id = t02.class_type_id ",
                    "AND t01.year_class_id = t02.year_class_id ",
                ") AS t ",
                "WHERE rate &lt; ${rade} )",
            "</when>",

            "SELECT",
            "t01.*,",
            "getSchoolName(t01.school_id) as school_name,",
            "getCourseTypeName(t01.class_type_id) class_type_name,",
            "getDictName(t01.year_class_id, 14) as year_class_name,",
            "getSubjectsName(t01.subjects_id) as subjects_name,",
            "count( t01.id ) AS count ",
            "FROM",
            "(",
            "SELECT",
            "t1.id,",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.lesson_type,",
            "t1.year_part,",
            "t1.quarter_num,",
            "t2.teacher_id, ",
            "t2.year_class_id,",
            "t2.course_type_id AS class_type_id, ",
            "t5.`name` AS student_name ,",
            "t6.`name` AS teacher_name,",
            "t2.`name` AS grade_name ",
            "FROM",
            "student_grade AS t1",
            "INNER JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "INNER JOIN book_text as t3 on t3.id = ${bookTextId}",
            "INNER JOIN book_text_year_class as t4 on t3.id = t4.book_text_id and t2.year_class_id = t4.year_class_id and t2.subjects_id = t4.subjects_id",
            "INNER JOIN student AS t5 ON t1.student_id = t5.id ",
            "INNER JOIN staff AS t6 ON t2.teacher_id = t6.id ",
            "<when test='subscribeType == 1 '>",
                "INNER JOIN subscribe_student_tb AS t7 ON t7.student_id = t1.student_id AND t7.subjects_id = t1.subjects_id AND t7.class_type_id = t2.course_type_id ",
            "</when>",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.school_id in(${schoolIdStr}) ",
            "<when test='subscribeType == 1 '>",
                "AND t7.id IS NOT NULL ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "AND t2.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                "AND t2.course_type_id = ${classTypeId}",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                "AND t2.teacher_id = ${teacherId}",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "AND t1.school_id = ${schoolId}",
            "</when>",
            "<when test='yearClassId!=null'>",// 避免幼儿园无法查询到
                "AND t2.year_class_id = ${yearClassId}",
            "</when>",
            "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                "AND t6.`name` LIKE '%${teacherName}%' ",
            "</when>",
            "<when test='studentName!=null and studentName!=&apos;&apos; '>",
                "AND t5.`name` LIKE '%${studentName}%' ",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
                "AND t1.grade_id = ${gradeId} ",
            "</when>",
            "AND t1.`status` = 1 ",
            "AND t1.reading_status IN ( 0, 1 ) ",
            "AND t1.lesson_type = ${lessonType} ",
            "GROUP BY",
            "<when test='bookTextType ==1 '>",
                "t1.student_id,",
            "</when>",
            "<when test='bookTextType ==2 '>",
                "t2.teacher_id,",
            "</when>",
            "t2.school_id,",
            "t2.course_type_id,",
            "t2.year_class_id,",
            "t2.subjects_id ",
            ") AS t01",
            "LEFT JOIN book_student AS t03 ON t01.year_part = t03.year_part ",
            "AND t01.quarter_num = t03.quarter_num ",
            "<when test='bookTextType ==1 '>",
                "AND t01.student_id = t03.student_id ",
            "</when>",
            "<when test='bookTextType ==2 '>",
                "AND t01.teacher_id = t03.teacher_id ",
            "</when>",
            "AND t01.subjects_id = t03.subjects_id ",
            "AND t01.lesson_type = t03.lesson_type ",
            "AND t01.class_type_id = t03.class_type_id ",
            "AND t01.year_class_id = t03.year_class_id ",
            "and t03.book_text_id IN ( ${bookTextId} )",
            //1学生用书，2教师用书
            "and t03.book_text_type =  ${bookTextType} ",
            "<when test='subscribeType == 1 '>",
                "AND t03.lesson_num = ${lessonNum}",
            "</when>",
            "LEFT JOIN book_exclude_subscription AS t04 ON t01.year_part = t04.year_part ",
            "AND t01.quarter_num = t04.quarter_num ",
            "AND t01.subjects_id = t04.subjects_id ",
            "AND t01.lesson_type = t04.lesson_type ",
            "AND t01.class_type_id = t04.class_type_id ",
            "AND t01.year_class_id = t04.year_class_id ",
            "AND t01.school_id = t04.school_id ",
            "AND t04.deleted = 0  ",
            "AND t04.book_text_id IN ( ${bookTextId} )",
            //1学生用书，2教师用书
            "AND t04.book_text_type =  ${bookTextType} ",
            "<when test='bookTextType == 1 '>",
                "AND t01.student_id = t04.student_id ",
            "</when>",
            "<when test='bookTextType == 2 '>",
                "AND t01.teacher_id = t04.teacher_id ",
            "</when>",
            "<when test='subscribeType == 1 '>",
                "AND t04.lesson_num = ${lessonNum}",
            "</when>",
            "WHERE",
            "t03.id IS NULL ",
            "AND t04.id IS NULL ",
            "GROUP BY",
            "t01.school_id,",
            "t01.subjects_id,",
            "t01.class_type_id",
            "<if test='isDetails == 1'>",
                ",t01.teacher_id ",
            "</if>",
            "<if test='isDetails == 2'>",
                ",t01.student_id ",
            "</if>",
            "</script>"})
    public abstract IPage<BookSchoolRs> getOrderSchool(IPage<?> page,
                                                       @Param("yearPart") Integer yearPart,
                                                       @Param("quarterNum") Integer quarterNum,
                                                       @Param("subjectsId") String subjectsId,
                                                       @Param("classTypeId") String classTypeId,
                                                       @Param("lessonType") Integer lessonType,
                                                       @Param("schoolIdStr") String schoolIdStr,
                                                       @Param("bookTextId") Long bookTextId,
                                                       @Param("bookTextType") Integer bookTextType,
                                                       @Param("isDetails") Integer isDetails,
                                                       @Param("teacherId") Long teacherId,
                                                       @Param("schoolId") Long schoolId,
                                                       @Param("yearClassId") Integer yearClassId,
                                                       @Param("teacherName") String teacherName,
                                                       @Param("studentName") String studentName,
                                                       @Param("gradeId") Long gradeId,
                                                       @Param("lessonNum") Integer lessonNum,
                                                       @Param("beforeLessonNum") Integer beforeLessonNum,
                                                       @Param("rade") Integer rade,
                                                       @Param("subscribeType") Integer subscribeType
    );

    @Select({"<script>",
            "<when test='subscribeType == 1 '>",
                "WITH subscribe_student_tb AS (",
                "SELECT * FROM (",
                    "SELECT ",
                    "t01.id,",
                    "t01.student_name,",
                    "t01.student_id,",
                    "t01.class_type_id,",
                    "t01.year_class_id,",
                    "t01.school_id,",
                    "t03.rade,",
                    "t02.subjects_id,",
                    "lowest_shutnum,",
                    "IFNULL( t02.right_num, 0 ) AS right_num,",
                    "IFNULL( t02.amend_num, 0 ) AS amend_num,",
                    "CASE WHEN lowest_shutnum &gt; 0 THEN ROUND( IFNULL( right_num / lowest_shutnum, 0 ) * 100, 2 ) ELSE 0 END AS rate ",
                    "FROM math_db.per_student_account AS t01",
                    "INNER JOIN (",
                        "SELECT * FROM math_db.cha_plan",
                        "WHERE",
                        "year_part = ${yearPart}",
                        "AND quarter_num = ${quarterNum}",
                        "AND task_category_id = 1 ",
                        "AND deleted = 0 ",
                        "AND lesson_num = ${beforeLessonNum}",
                        "AND is_first = 1 ",
                    ") AS t02 ON t01.student_id = t02.student_id ",
                    "AND t01.class_type_id = t02.class_type_id ",
                    "AND t01.year_class_id = t02.year_class_id ",
                    "LEFT JOIN xmy_erp_db.book_accuracy_set AS t03 ON t03.class_type_id = t01.class_type_id ",
                    "AND t03.subjects_id = t02.subjects_id ",
                ") AS t ",
                "WHERE rate &lt; rade )",
            "</when>",

            "SELECT",
            "t01.*,",
            "getSchoolName(t01.school_id) as school_name,",
            "getCourseTypeName(t01.class_type_id) class_type_name,",
            "getDictName(t01.year_class_id, 14) as year_class_name,",
            "getSubjectsName(t01.subjects_id) as subjects_name,",
            "count( t01.id ) AS count ",
            "FROM",
            "(",
            "SELECT",
            "t1.id,",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.lesson_type,",
            "t1.year_part,",
            "t1.quarter_num,",
            "t2.year_class_id,",
            "t2.teacher_id,",
            "t2.course_type_id AS class_type_id ",
            "FROM",
            "student_grade AS t1",
            "INNER JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "INNER JOIN book_text as t3 on t3.id = ${bookTextId}",
            "INNER JOIN book_text_year_class as t4 on t3.id = t4.book_text_id and t2.year_class_id = t4.year_class_id and t2.subjects_id = t4.subjects_id",
            "<when test='subscribeType == 1 '>",
                "INNER JOIN subscribe_student_tb AS t7 ON t7.student_id = t1.student_id AND t7.subjects_id = t1.subjects_id AND t7.class_type_id = t2.course_type_id ",
            "</when>",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and t2.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "and t2.course_type_id = ${classTypeId}",
            "</when>",
            "AND t1.`status` = 1 ",
            "AND t1.reading_status IN ( 0, 1 ) ",
            "AND t1.lesson_type = ${lessonType} ",
            "<when test='subscribeType == 1 '>",
                "AND t7.id IS NOT NULL ",
            "</when>",
            "GROUP BY",
//            "t1.student_id,",
            "<when test='bookTextType ==1 '>",
            "t1.student_id,",
            "</when>",
            "<when test='bookTextType ==2 '>",
            "t2.teacher_id,",
            "</when>",
            "t2.course_type_id,",
            "t2.subjects_id, ",
            "t2.year_class_id,",
            "t2.school_id ",
            ") AS t01",
            "LEFT JOIN book_student AS t03 ON t01.year_part = t03.year_part ",
            "AND t01.quarter_num = t03.quarter_num ",
            "<when test='bookTextType ==1 '>",
                "AND t01.student_id = t03.student_id ",
            "</when>",
            "<when test='bookTextType ==2 '>",
                "AND t01.teacher_id = t03.teacher_id ",
            "</when>",
            "AND t01.student_id = t03.student_id ",
            "AND t01.subjects_id = t03.subjects_id ",
            "AND t01.lesson_type = t03.lesson_type ",
            "AND t01.class_type_id = t03.class_type_id ",
            "AND t01.year_class_id = t03.year_class_id ",
//            "AND t01.school_id = t03.school_id ",
            "and t03.book_text_id IN ( ${bookTextId} )",
            //1学生用书，2教师用书
            "and t03.book_text_type = ${bookTextType} ",
            "<when test='subscribeType == 1 '>",
                "AND t03.lesson_num = ${lessonNum}",
            "</when>",
            "LEFT JOIN book_exclude_subscription AS t04 ON t01.year_part = t04.year_part ",
            "AND t01.quarter_num = t04.quarter_num ",
            "AND t01.subjects_id = t04.subjects_id ",
            "AND t01.lesson_type = t04.lesson_type ",
            "AND t01.class_type_id = t04.class_type_id ",
            "AND t01.year_class_id = t04.year_class_id ",
            "AND t01.school_id = t04.school_id ",
            "AND t04.deleted = 0  ",
            "AND t04.book_text_id IN ( ${bookTextId} )",
            //1学生用书，2教师用书
            "AND t04.book_text_type =  ${bookTextType} ",
            "<when test='bookTextType == 1 '>",
                "AND t01.student_id = t04.student_id ",
            "</when>",
            "<when test='bookTextType == 2 '>",
                "AND t01.teacher_id = t04.teacher_id ",
            "</when>",
            "<when test='subscribeType == 1 '>",
                "AND t04.lesson_num = ${lessonNum}",
            "</when>",
            "WHERE",
            "t03.id IS NULL ",
            "AND t04.id IS NULL ",
            "GROUP BY",
            "t01.subjects_id,",
            "t01.class_type_id",
            "<when test='isDetails == 1 '>",
                "t01.student_id ",
            "</when>",
            "</script>"})
    public abstract List<BookClassTypeCountRs> getOrderSchoolCountForClassType(@Param("yearPart") Integer yearPart,
                                                                               @Param("quarterNum") Integer quarterNum,
                                                                               @Param("subjectsId") String subjectsId,
                                                                               @Param("classTypeId") String classTypeId,
                                                                               @Param("lessonType") Integer lessonType,
                                                                               @Param("bookTextId") Long bookTextId,
                                                                               @Param("bookTextType") Integer bookTextType,
                                                                               @Param("lessonNum") Integer lessonNum,
                                                                               @Param("beforeLessonNum") Integer beforeLessonNum,
                                                                               @Param("rade") Integer rade,
                                                                               @Param("isDetails") Integer isDetails,
                                                                               @Param("subscribeType") Integer subscribeType
    );



    @Select({"<script>",
            "<when test='subscribeType == 1 '>",
                "WITH subscribe_student_tb AS (",
                    "SELECT * FROM (",
                        "SELECT ",
                        "t01.id,",
                        "t01.student_name,",
                        "t01.student_id,",
                        "t01.class_type_id,",
                        "t01.year_class_id,",
                        "t01.school_id,",
                        "t02.subjects_id,",
                        "lowest_shutnum,",
                        "IFNULL( t02.right_num, 0 ) AS right_num,",
                        "IFNULL( t02.amend_num, 0 ) AS amend_num,",
                        "CASE WHEN lowest_shutnum &gt; 0 THEN ROUND( IFNULL( right_num / lowest_shutnum, 0 ) * 100, 2 ) ELSE 0 END AS rate ",
                        "FROM ",
                        "<if test='subjectsId == 2'>",
                            "math_db.per_student_account AS t01",
                        "</if>",
                        "<if test='subjectsId == 3'>",
                            "physics_db.per_student_account AS t01",
                        "</if>",
                        "INNER JOIN (",
                        "SELECT * FROM ",
                        "<if test='subjectsId == 2'>",
                            "math_db.cha_plan",
                        "</if>",
                        "<if test='subjectsId == 3'>",
                            "physics_db.cha_plan",
                        "</if>",
                        "WHERE",
                        "year_part = ${yearPart}",
                        "AND quarter_num = ${quarterNum}",
                        "AND task_category_id = 1 ",
                        "AND deleted = 0 ",
                        "AND lesson_num = ${beforeLessonNum}",
                        "AND is_first = 1 ",
                    ") AS t02 ON t01.student_id = t02.student_id ",
                    "AND t01.class_type_id = t02.class_type_id ",
                    "AND t01.year_class_id = t02.year_class_id ",
                    ") AS t ",
                "WHERE rate &lt; ${rade} )",
            "</when>",

            "SELECT",
            "t01.*,",
            "getDictName(t01.year_class_id, 14) as year_class_name,",
            "count( t01.id ) AS count ",
            "FROM",
            "(",
            "SELECT",
            "t1.id,",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.lesson_type,",
            "t1.year_part,",
            "t1.quarter_num,",
            "t2.year_class_id,",
            "t2.teacher_id,",
            "t2.course_type_id AS class_type_id ",
            "FROM",
            "student_grade AS t1",
            "INNER JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "INNER JOIN book_text as t3 on t3.id = ${bookTextId}",
            "INNER JOIN book_text_year_class as t4 on t3.id = t4.book_text_id and t2.year_class_id = t4.year_class_id and t2.subjects_id = t4.subjects_id",
            "<when test='subscribeType == 1 '>",
                "INNER JOIN subscribe_student_tb AS t7 ON t7.student_id = t1.student_id AND t7.subjects_id = t1.subjects_id AND t7.class_type_id = t2.course_type_id ",
            "</when>",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.school_id = ${schoolId} ",
            "<when test='subscribeType == 1 '>",
                "AND t7.id IS NOT NULL ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "and t2.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                "and t2.course_type_id = ${classTypeId}",
            "</when>",
            "<when test='yearClassId!=null'>",// 避免幼儿园无法查询到
                "and t2.year_class_id = ${yearClassId}",
            "</when>",
            "<when test='isDetails == 1'>",
                "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                    "and t2.teacher_id = ${teacherId} ",
                "</when>",
            "</when>",
            "AND t1.`status` = 1 ",
            "AND t1.reading_status IN ( 0, 1 ) ",
            "AND t1.lesson_type = ${lessonType} ",
            "GROUP BY",
//            "t1.student_id,",
            "<when test='bookTextType ==1 '>",
                "t1.student_id,",
            "</when>",
            "<when test='bookTextType ==2 '>",
                "t2.teacher_id,",
            "</when>",
            "t2.course_type_id,",
            "t2.year_class_id,",
            "t2.subjects_id, ",
            "t2.school_id ",
            ") AS t01",
            "LEFT JOIN book_student AS t03 ON t01.year_part = t03.year_part ",
            "AND t01.quarter_num = t03.quarter_num ",
            "<when test='bookTextType ==1 '>",
                "AND t01.student_id = t03.student_id ",
            "</when>",
            "<when test='bookTextType ==2 '>",
                "AND t01.teacher_id = t03.teacher_id ",
            "</when>",
            "AND t01.student_id = t03.student_id ",
            "AND t01.subjects_id = t03.subjects_id ",
            "AND t01.lesson_type = t03.lesson_type ",
            "AND t01.class_type_id = t03.class_type_id ",
            "AND t01.year_class_id = t03.year_class_id ",
            "<when test='bookTextId!=null and bookTextId!=&apos;&apos; '>",
                "and t03.book_text_id IN ( ${bookTextId} )",
            "</when>",
            //1学生用书，2教师用书
            "and t03.book_text_type = ${bookTextType} ",
            "<when test='subscribeType == 1 '>",
                "AND t03.lesson_num = ${lessonNum}",
            "</when>",
            "LEFT JOIN book_exclude_subscription AS t04 ON t01.year_part = t04.year_part ",
            "AND t01.quarter_num = t04.quarter_num ",
            "AND t01.subjects_id = t04.subjects_id ",
            "AND t01.lesson_type = t04.lesson_type ",
            "AND t01.class_type_id = t04.class_type_id ",
            "AND t01.year_class_id = t04.year_class_id ",
            "AND t01.school_id = t04.school_id ",
            "AND t04.deleted = 0  ",
            "AND t04.book_text_id IN ( ${bookTextId} )",
            //1学生用书，2教师用书
            "AND t04.book_text_type =  ${bookTextType} ",
            "<when test='bookTextType == 1 '>",
                "AND t01.student_id = t04.student_id ",
            "</when>",
            "<when test='bookTextType == 2 '>",
                "AND t01.teacher_id = t04.teacher_id ",
            "</when>",
            "<when test='subscribeType == 1 '>",
                "AND t04.lesson_num = ${lessonNum}",
            "</when>",

            "WHERE",
            "t03.id IS NULL ",
            "AND t04.id IS NULL ",
            "GROUP BY",
            "t01.school_id,",
            "t01.class_type_id,",
            "t01.year_class_id",
            "<when test='isDetails == 1'>",
                ",t01.teacher_id ",
            "</when>",
            "</script>"})
    public abstract List<BookSchoolCountRs> getSubCountList(@Param("yearPart") Integer yearPart,
                                                            @Param("quarterNum") Integer quarterNum,
                                                            @Param("schoolId") Long schoolId,
                                                            @Param("subjectsId") String subjectsId,
                                                            @Param("classTypeId") String classTypeId,
                                                            @Param("lessonType") Integer lessonType,
                                                            @Param("bookTextId") Long bookTextId,
                                                            @Param("bookTextType") Integer bookTextType,
                                                            @Param("isDetails") Integer isDetails,
                                                            @Param("teacherId") Long teacherId,
                                                            @Param("yearClassId") Integer yearClassId,
                                                            @Param("lessonNum") Integer lessonNum,
                                                            @Param("beforeLessonNum") Integer beforeLessonNum,
                                                            @Param("rade") Integer rade,
                                                            @Param("subscribeType") Integer subscribeType
    );


    /**
     * 获取订购信息
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @param classTypeId
     * @param lessonType
     * @param orderType 1已订购，2下单，3领取，4发放
     * @return
     */
    @Select({"<script>",
//            "<when test='subscribeType == 1 '>",
//                "WITH subscribe_student_tb AS (",
//                    "SELECT * FROM (",
//                        "SELECT ",
//                        "t01.id,",
//                        "t01.student_name,",
//                        "t01.student_id,",
//                        "t01.class_type_id,",
//                        "t01.year_class_id,",
//                        "t01.school_id,",
//                        "lowest_shutnum,",
//                        "IFNULL( t02.right_num, 0 ) AS right_num,",
//                        "IFNULL( t02.amend_num, 0 ) AS amend_num,",
//                        "CASE WHEN lowest_shutnum &gt; 0 THEN ROUND( IFNULL( right_num / lowest_shutnum, 0 ) * 100, 2 ) ELSE 0 END AS rate ",
//                        "FROM ",
//                        "math_db.per_student_account AS t01",
//                        "INNER JOIN (",
//                            "SELECT * FROM math_db.cha_plan ",
//                            "WHERE",
//                            "year_part = ${yearPart}",
//                            "AND quarter_num = ${quarterNum}",
//                            "AND task_category_id = 1 ",
//                            "AND deleted = 0 ",
//                            "AND lesson_num = ${beforeLessonNum}",
//                            "AND is_first = 1 ",
//                        ") AS t02 ON t01.student_id = t02.student_id ",
//                        "AND t01.class_type_id = t02.class_type_id ",
//                        "AND t01.year_class_id = t02.year_class_id ",
//                    ") AS t ",
//                "WHERE rate &lt; ${rade} )",
//            "</when>",

            "select * ,IF( fetch_count &gt;= all_count, 1, 0 ) AS fetch_status,IF( order_count &gt;= all_count, 1, 0 ) AS order_status from(",
                "SELECT",
                "t03.year_part,",
                "t03.quarter_num,",
                "t03.school_id,",
                "t03.class_type_id,",
                "t03.year_class_id,",
                "t03.subjects_id,",
                "t03.lesson_type,",
                "t03.teacher_id,",
                "getSchoolName(t03.school_id) as school_name,",
                "getCourseTypeName(t03.class_type_id) class_type_name,",
                "getDictName(t03.year_class_id, 14) as year_class_name,",
                "getSubjectsName(t03.subjects_id) as subjects_name,",
                "count( t03.id ) AS count,",
                "t03.order_id,",
                "t04.batch_name,",
                "t06.`name` AS student_name, ",
                "t07.`name` AS teacher_name, ",
                "t08.`name` AS grade_name, ",
                "t04.created_at,",
                //"<if test='orderType!=null and orderType == 3'>",
                    "count(t03.id) as all_count,",
                    "SUM(( CASE WHEN t05.id IS NULL THEN 0 ELSE 1 END )) AS fetch_count,",
//                    "SUM( ( CASE WHEN t03.`status` =2 THEN 1 ELSE 0 END ) ) AS order_count,",
                    "SUM( ( CASE WHEN t03.`status` >=2 THEN 1 ELSE 0 END ) ) AS order_count,",
                    //"(CASE WHEN t05.id IS NULL THEN 0 ELSE 1 END ) AS fetch_status,",
                    "MAX(t05.created_at) AS fetch_date_time,",
                //"</if>",
                "${status} as status_param,",
                "${orderType} as order_type,",
                "${orderSubType} as order_sub_type,",
                "${type} as type",
                "FROM",
                "book_student AS t03",
                "INNER JOIN book_order as t04 on t03.order_id = t04.id",
                "LEFT JOIN book_fetch AS t05 ON t05.order_id = t04.id AND t05.school_id = t03.school_id  AND t05.subjects_id = t03.subjects_id AND t05.class_type_id = t03.class_type_id AND t05.year_class_id = t03.year_class_id",
                "INNER JOIN student AS t06 ON t03.student_id = t06.id ",
                "INNER JOIN staff AS t07 ON t03.teacher_id = t07.id ",
                "INNER JOIN grade AS t08 ON t03.grade_id = t08.id ",
                "<when test='subscribeType == 1 '>",
//                    "INNER JOIN subscribe_student_tb AS t09 ON t09.student_id = t03.student_id ",
                "</when>",
                "WHERE",
                "t03.year_part = ${yearPart} ",
                "AND t03.quarter_num = ${quarterNum} ",
                "AND t03.school_id in(${schoolIdStr}) ",
                "<when test='subscribeType == 1 '>",
//                    "AND t09.id IS NOT NULL ",
                    "AND t03.lesson_num = ${lessonNum}",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                    "and t03.subjects_id = ${subjectsId}",
                "</when>",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                    "and t03.class_type_id = ${classTypeId}",
                "</when>",
                "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                    "and t03.teacher_id = ${teacherId}",
                "</when>",
                "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                    "and t03.school_id = ${schoolId}",
                "</when>",
                "<when test='yearClassId!=null'>",// 避免幼儿园无法查询到
                    "and t03.year_class_id = ${yearClassId}",
                "</when>",
                "<when test='bookTextId!=null and bookTextId!=&apos;&apos; '>",
                    "and t03.book_text_id IN ( ${bookTextId} )",
                "</when>",
                "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                    "AND t07.`name` LIKE '%${teacherName}%' ",
                "</when>",
                "<when test='studentName!=null and studentName!=&apos;&apos; '>",
                    "AND t06.`name` LIKE '%${studentName}%' ",
                "</when>",
                "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
                    "AND t03.grade_id = ${gradeId} ",
                "</when>",
                "<if test='orderType!=null and orderType == 2'>",
                // 下单
//                "and t04.status = 2",
                "and t04.is_order_status = 1",
                "and t03.status &gt;= 2 ",
                "</if>",
                "<if test='orderType!=null and orderType == 3'>",
                //原来领取
//                "and t04.status in(2,3)",
                  "and t04.is_order_status = 1",
                  "and t03.status &gt;= 2 ",
                  //"and t03.status &gt;= 2 ",
                    "<if test='orderSubType!=null and orderSubType == 1'>",
                        "and t05.id is not null",
                    "</if>",
                    "<if test='orderSubType!=null and orderSubType == 0'>",
                        "and t05.id is null",
                    "</if>",
                "</if>",
                "<if test='orderType!=null and orderType == 4'>",
                "and t04.status in(3)",
                    "<if test='orderSubType!=null and orderSubType == 1'>",
                        "and t03.status = 4",
                    "</if>",
                    "<if test='orderSubType!=null and orderSubType == 0'>",
                        "and t03.status &lt;&gt; 4",
                    "</if>",
                "</if>",
                "<when test='orderId!=null'>",
                    "and t04.id = ${orderId}",
                "</when>",
                //1学生用书，2教师用书
                "AND t03.book_text_type = ${bookTextType}",
                "GROUP BY",
                "<if test='type == 1'>",
                    "t04.id",
                "</if>",
                "<if test='type == 2'>",
                    "t04.id,",
                    "t03.school_id,",
                    "t03.class_type_id",
                "</if>",
                "<when test='isDetails == 1'>",
                    ",t03.teacher_id ",
                "</when>",
                "<when test='isDetails == 2'>",
                    ",t03.student_id ",
                "</when>",
                "order by t04.batch_num desc",
            ") as t",
            "</script>"})
    public abstract IPage<BookSchoolRs> getYetOrderSchool(IPage<?> page,
                                                          @Param("orderId") Long orderId,
                                                          @Param("yearPart") Integer yearPart,
                                                          @Param("quarterNum") Integer quarterNum,
                                                          @Param("subjectsId") String subjectsId,
                                                          @Param("classTypeId") String classTypeId,
                                                          @Param("lessonType") Integer lessonType,
                                                          @Param("type") Integer type,
                                                          @Param("status") Integer status,
                                                          @Param("orderType") Integer orderType,
                                                          @Param("orderSubType") Integer orderSubType,
                                                          @Param("schoolIdStr") String schoolIdStr,
                                                          @Param("bookTextId") Long bookTextId,
                                                          @Param("bookTextType") Integer bookTextType,
                                                          @Param("isDetails") Integer isDetails, // 0列表，1老师详情，2学员详情
                                                          @Param("teacherId") Long teacherId,
                                                          @Param("schoolId") Long schoolId,
                                                          @Param("yearClassId") Integer yearClassId,
                                                          @Param("teacherName") String teacherName,
                                                          @Param("studentName") String studentName,
                                                          @Param("gradeId") Long gradeId,
                                                          @Param("subscribeType") Integer subscribeType,
                                                          @Param("lessonNum") Integer lessonNum,
                                                          @Param("beforeLessonNum") Integer beforeLessonNum,
                                                          @Param("rade") Integer rade
    );

    @Select({"<script>",
            "select * ,IF( fetch_count &gt;= all_count, 1, 0 ) AS fetch_status,IF( order_count &gt;= all_count, 1, 0 ) AS order_status from(",
            "SELECT",
            "t03.year_part,",
            "t03.quarter_num,",
            "t03.school_id,",
            "t03.class_type_id,",
            "t03.year_class_id,",
            "t03.subjects_id,",
            "t03.lesson_type,",
            "getSchoolName(t03.school_id) as school_name,",
            "getCourseTypeName(t03.class_type_id) class_type_name,",
            "getDictName(t03.year_class_id, 14) as year_class_name,",
            "getSubjectsName(t03.subjects_id) as subjects_name,",
            "count( t03.id ) AS count,",
            "t03.order_id,",
            "t04.batch_name,",
            //"<if test='orderType!=null and orderType == 3'>",
            "count(t03.id) as all_count,",
            "SUM(( CASE WHEN t05.id IS NULL THEN 0 ELSE 1 END )) AS fetch_count,",
            "SUM( ( CASE WHEN t03.`status` >=2 THEN 1 ELSE 0 END ) ) AS order_count,",
            //"(CASE WHEN t05.id IS NULL THEN 0 ELSE 1 END ) AS fetch_status,",
            "MAX(t05.created_at) AS fetch_date_time,",
            //"</if>",
            "${status} as status_param,",
            "${orderType} as order_type,",
            "${orderSubType} as order_sub_type,",
            "${type} as type",
            "FROM",
            "book_student AS t03",
            "INNER JOIN book_order as t04 on t03.order_id = t04.id",
            "LEFT JOIN book_fetch AS t05 ON t05.order_id = t04.id  AND t05.school_id = t03.school_id  AND t05.subjects_id = t03.subjects_id AND t05.class_type_id = t03.class_type_id",
            "WHERE",
            "t03.year_part = ${yearPart} ",
            "AND t03.quarter_num = ${quarterNum} ",
            "AND t03.school_id in(${schoolIdStr}) ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and t03.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "and t03.class_type_id = ${classTypeId}",
            "</when>",

            "<if test='orderType!=null and orderType == 2'>",
            //原先
//                "and t04.status = 2",
            "and t04.is_order_status = 1",
            "</if>",
            "<if test='orderType!=null and orderType == 3'>",
            //原来领取
//                "and t04.status in(2,3)",
            "and t04.is_order_status = 1",
//            "and t03.status &gt;= 2 ",
            "and t03.status &gt;= 2 ",
            "<if test='orderSubType!=null and orderSubType == 1'>",
            "and t05.id is not null",
            "</if>",
            "<if test='orderSubType!=null and orderSubType == 0'>",
            "and t05.id is null",
            "</if>",
            "</if>",
            "<if test='orderType!=null and orderType == 4'>",
            "and t04.status in(3)",
            "<if test='orderSubType!=null and orderSubType == 1'>",
            "and t03.status = 4",
            "</if>",
            "<if test='orderSubType!=null and orderSubType == 0'>",
            "and t03.status &lt;&gt; 4",
            "</if>",
            "</if>",
            "<when test='orderId!=null'>",
            "and t04.id = ${orderId}",
            "</when>",
            //"<when test='status!=null'>",
            //"and t04.status = ${status}",
            //"</when>",
            "GROUP BY",
            "<if test='type  == 1'>",
            "t04.id",
            "</if>",
            "<if test='type  == 2'>",
            "t04.id,",
            "t03.school_id,",
            "t03.class_type_id",
            "</if>",
            ") as t",
            "<if test='orderStatus!=null '>",
            "HAVING order_status = ${orderStatus} ",
            "</if>",
            "</script>"})
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "bookCountList",
                    column = "{yearPart=year_part ,quarterNum=quarter_num,schoolId=school_id,subjectsId=subjects_id,classTypeId=class_type_id,lessonType=lesson_type,orderId=order_id,status=status_param,type=type,orderType=order_type,orderSubType=order_sub_type}",
                    many = @Many(select = "com.xmy.cultivate.mapper.BookOrderMapper.getYetSubCountList")
            )
    })
    public abstract List<BookSchoolRs> getDistributeOrderSchool(@Param("orderId") Long orderId,
                                                         @Param("yearPart") Integer yearPart,
                                                         @Param("quarterNum") Integer quarterNum,
                                                         @Param("subjectsId") String subjectsId,
                                                         @Param("classTypeId") String classTypeId,
                                                         @Param("lessonType") Integer lessonType,
                                                         @Param("type") Integer type,
                                                         @Param("status") Integer status,
                                                         @Param("orderType") Integer orderType,
                                                         @Param("orderSubType") Integer orderSubType,
                                                         @Param("orderStatus") Integer orderStatus,
                                                         @Param("schoolIdStr") String schoolIdStr
    );

    /**
     * 获取教材发放数据
     *
     * @param orderId
     * @param orderType
     * @param orderSubType 1已发放，0未发放
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @param classTypeId
     * @param yearClassId
     * @param disType 0校区发放 1教师发放 2学生发放
     * @param orderStatus
     * @param schoolId
     * @param teacherName
     * @param studentName
     * @param gradeId
     */
    @Select({"<script>",
//            "<when test='subscribeType == 1 '>",
//                "WITH subscribe_student_tb AS (",
//                "SELECT * FROM (",
//                    "SELECT ",
//                    "t01.id,",
//                    "t01.student_name,",
//                    "t01.student_id,",
//                    "t01.class_type_id,",
//                    "t01.year_class_id,",
//                    "t01.school_id,",
//                    "t03.rade,",
//                    "t02.subjects_id,",
//                    "lowest_shutnum,",
//                    "IFNULL( t02.right_num, 0 ) AS right_num,",
//                    "IFNULL( t02.amend_num, 0 ) AS amend_num,",
//                    "CASE WHEN lowest_shutnum &gt; 0 THEN ROUND( IFNULL( right_num / lowest_shutnum, 0 ) * 100, 2 ) ELSE 0 END AS rate ",
//                    "FROM ",
//                    "math_db.per_student_account AS t01",
//                    "INNER JOIN (",
//                        "SELECT * FROM math_db.cha_plan ",
//                        "WHERE",
//                        "year_part = ${yearPart}",
//                        "AND quarter_num = ${quarterNum}",
//                        "AND task_category_id = 1 ",
//                        "AND deleted = 0 ",
//                        "AND lesson_num = ${beforeLessonNum}",
//                        "AND is_first = 1 ",
//                    ") AS t02 ON t01.student_id = t02.student_id ",
//                    "AND t01.class_type_id = t02.class_type_id ",
//                    "AND t01.year_class_id = t02.year_class_id ",
//                    "LEFT JOIN xmy_erp_db.book_accuracy_set AS t03 ON t03.class_type_id = t01.class_type_id ",
//                    "AND t03.subjects_id = t02.subjects_id ",
//                ") AS t ",
//                "WHERE rate &lt; rade )",
//            "</when>",

            "select * ,",
            "IF( fetch_count &gt;= count, 1, 0 ) AS fetch_status,IF( order_count &gt;= count, 1, 0 ) AS order_status,IF( info_count &gt;= 1, 0, 1 ) AS info_status ",
            "from( ",
            "SELECT",
            "t03.id,",
            "t03.year_part,",
            "t03.quarter_num,",
            "t03.school_id,",
            "t03.class_type_id,",
            "t03.year_class_id,",
            "t03.subjects_id,",
            "t03.lesson_type,",
            "t03.teacher_id,",
            "t03.student_id,",
            "getGradeNameForId(t06.grade_id) AS grade_name,",
            "getSchoolName(t03.school_id) as school_name,",
            "getCourseTypeName(t03.class_type_id) class_type_name,",
            "getDictName(t03.year_class_id, 14) as year_class_name,",
            "getSubjectsName(t03.subjects_id) as subjects_name,",
            "getStaffNameForId ( t03.teacher_id ) AS teacher_name,",
            "getStudentNameForId( t03.student_id ) AS student_name,",
            "t07.created_at AS grant_date_time,",
            "count( t03.id ) AS count,",
            "t03.order_id,",
            "t04.batch_name,",
            "SUM( ( CASE WHEN t05.id IS NULL THEN 0 ELSE 1 END ) ) AS fetch_count,",
            "SUM( ( CASE WHEN t03.`status` >=2 THEN 1 ELSE 0 END ) ) AS order_count,",
            "MAX(t05.created_at) AS fetch_date_time,",
            "SUM(CASE WHEN t07.id IS NULL THEN 1 ELSE 0 END )AS info_count ",
            "FROM",
            "book_student AS t03",
            "INNER JOIN book_order as t04 on t03.order_id = t04.id",
            "LEFT JOIN book_fetch AS t05 ON t05.order_id = t04.id  AND t05.school_id = t03.school_id  AND t05.subjects_id = t03.subjects_id AND t05.class_type_id = t03.class_type_id AND t05.year_class_id = t03.year_class_id",
            "LEFT JOIN student_grade AS t06 ON t06.year_part = t03.year_part ",
            "AND t06.quarter_num = t03.quarter_num ",
            "AND t06.school_id = t03.school_id ",
            "AND t06.subjects_id = t03.subjects_id",
            "AND t06.lesson_type = t03.lesson_type ",
            "AND t06.student_id = t03.student_id ",
//            "AND t06.reading_status IN (0,1) ",
            "AND t06.`status` = 1  ",
            "AND t06.`reading_status` &lt;&gt; 3 ",
            "AND t06.year_class_id = t03.year_class_id ",
//            "AND t04.class_type_id = ( SELECT course_type_id FROM grade WHERE grade.id = t06.grade_id LIMIT 1) ",
            "AND t06.is_last = 1 ",
            "AND t06.deleted = 0 ",
            "LEFT JOIN book_grant_info AS t07 ON t03.id = t07.book_student_id  ",
            "<if test='disType == 0 '>",
                "AND t07.`status` = 4 ",
            "</if>",
            "<if test='(disType == 2 and isDetails == 0) or (disType == 1)'>",
                "AND t07.`status` = 5 ",
            "</if>",
            "<when test='subscribeType == 1 '>",
//                "INNER JOIN subscribe_student_tb AS t08 ON t08.student_id = t03.student_id AND t08.subjects_id = t03.subjects_id AND t08.class_type_id = t03.class_type_id ",
            "</when>",
            "WHERE",
            "t03.year_part = ${yearPart} ",
            "AND t03.quarter_num = ${quarterNum} ",
            "AND t03.school_id in(${schoolIdStr})",
            "<when test='subscribeType == 1 '>",
//                "AND t08.id IS NOT NULL ",
                "AND t03.lesson_num = ${lessonNum}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "AND t03.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                "AND t03.class_type_id = ${classTypeId}",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "AND t03.school_id IN (${schoolId})",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                "AND t03.teacher_id IN (${teacherId}) ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                "AND t03.year_class_id IN (${yearClassId}) ",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
                "AND t06.grade_id IN (${gradeId})",
            "</when>",
            "<when test='orderId!=null'>",
                "AND t04.id = ${orderId}",
            "</when>",
            "<when test='bookTextId!=null and bookTextId!=&apos;&apos; '>",
                "AND t03.book_text_id IN (${bookTextId})",
            "</when>",
            //1学生用书，2教师用书
            "AND t03.book_text_type = ${bookTextType}",


            "<if test='disType == 0'>",
                // 发放到校区
                "AND t03.`status` >= 3 ",
            "</if>",

            "<if test='disType == 1'>",
                // 发放到教师（校区详情、教师列表）
                "AND t03.`status` >= 4 ",
            "</if>",

            "<if test='disType == 2'>",
                // 发放到学生（教师详情、学生列表）
                "AND t03.`status` >= 4 ",
            "</if>",

            "<if test='orderSubType!=null and orderSubType == 0'>",
                "AND t07.id IS NULL",
            "</if>",
            "<if test='orderSubType!=null and orderSubType == 1'>",
                "AND t07.id IS NOT NULL",
            "</if>",

            "GROUP BY",
            "<if test='type == 1'>",
                "t03.class_type_id,t03.subjects_id ",
            "</if>",
            "<if test='type == 2'>",
                "t03.class_type_id",
                "<if test='disType == 0'>",
                    ",t03.school_id ",
                    "<if test='isDetails == 1'>",
                        // 校区中的老师详情
                        ",t03.teacher_id ",
                    "</if>",
                    "<if test='isDetails == 2'>",
                        // 校区中的学生详情
                        ",t03.student_id ",
                    "</if>",
                "</if>",
                "<if test='disType == 1'>",
                    ",t03.school_id,t03.teacher_id ",
                    "<if test='isDetails == 1'>",
                        // 教师中的学生详情
                        ",t03.student_id ",
                    "</if>",
                "</if>",
                "<if test='disType == 2'>",
                    // 学生数据
                    ",t03.school_id,t03.student_id ",
                "</if>",
            "</if>",
            ") as t",
            "WHERE 1=1",
            "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                "AND teacher_name LIKE '%${teacherName}%' ",
            "</when>",
            "<when test='studentName!=null and studentName!=&apos;&apos; '>",
                "AND student_name LIKE '%${studentName}%' ",
            "</when>",
            "<when test='idList!=null and idList!=&apos;&apos; '>",
                "AND id IN (${idList})",
            "</when>",
            "<if test='orderStatus!=null '>",
                "HAVING order_status = ${orderStatus} ",
            "</if>",
            "</script>"})
    public abstract IPage<BookSchoolRs> getDistributeOrderSchoolDetails(IPage<?> page,
                                                                        @Param("orderId") Long orderId,
                                                                        @Param("yearPart") Integer yearPart,
                                                                        @Param("quarterNum") Integer quarterNum,
                                                                        @Param("subjectsId") String subjectsId,
                                                                        @Param("classTypeId") String classTypeId,
                                                                        @Param("yearClassId") String yearClassId,
                                                                        @Param("lessonType") Integer lessonType,
                                                                        @Param("type") Integer type,
                                                                        @Param("orderType") Integer orderType,
                                                                        @Param("orderSubType") Integer orderSubType,
                                                                        @Param("orderStatus") Integer orderStatus,
                                                                        @Param("disType") Integer disType,
                                                                        @Param("schoolId") String schoolId,
                                                                        @Param("teacherId") String teacherId,
                                                                        @Param("idList") String idList,
                                                                        @Param("teacherName") String teacherName,
                                                                        @Param("studentName") String studentName,
                                                                        @Param("gradeId") Long gradeId,
                                                                        @Param("schoolIdStr") String schoolIdStr,
                                                                        @Param("isDetails") Integer isDetails,
                                                                        @Param("bookTextId") Long bookTextId,
                                                                        @Param("bookTextType") Integer bookTextType,
                                                                        @Param("subscribeType") Integer subscribeType,
                                                                        @Param("lessonNum") Integer lessonNum,
                                                                        @Param("beforeLessonNum") Integer beforeLessonNum,
                                                                        @Param("rade") Integer rade
    );

    @Select({"<script>",
//            "<when test='subscribeType == 1 '>",
//                "WITH subscribe_student_tb AS (",
//                    "SELECT * FROM (",
//                    "SELECT ",
//                    "t01.id,",
//                    "t01.student_name,",
//                    "t01.student_id,",
//                    "t01.class_type_id,",
//                    "t01.year_class_id,",
//                    "t01.school_id,",
//                    "lowest_shutnum,",
//                    "IFNULL( t02.right_num, 0 ) AS right_num,",
//                    "IFNULL( t02.amend_num, 0 ) AS amend_num,",
//                    "CASE WHEN lowest_shutnum &gt; 0 THEN ROUND( IFNULL( right_num / lowest_shutnum, 0 ) * 100, 2 ) ELSE 0 END AS rate ",
//                    "FROM ",
//                    "math_db.per_student_account AS t01",
//                    "INNER JOIN (",
//                        "SELECT * FROM math_db.cha_plan ",
//                        "WHERE",
//                        "year_part = ${yearPart}",
//                        "AND quarter_num = ${quarterNum}",
//                        "AND task_category_id = 1 ",
//                        "AND deleted = 0 ",
//                        "AND lesson_num = ${beforeLessonNum}",
//                        "AND is_first = 1 ",
//                    ") AS t02 ON t01.student_id = t02.student_id ",
//                    "AND t01.class_type_id = t02.class_type_id ",
//                    "AND t01.year_class_id = t02.year_class_id ",
//                ") AS t ",
//                "WHERE rate &lt; ${rade} )",
//            "</when>",

            "SELECT",
            "t03.id,",
            "t03.school_id,",
            "t03.student_id,",
            "t03.subjects_id,",
            "t03.lesson_type,",
            "t03.year_part,",
            "t03.quarter_num,",
            "t03.year_class_id,",
            "t03.class_type_id,",
            "getDictName(t03.year_class_id, 14) as year_class_name,",
            "count( t03.id ) AS count ",
            "FROM",
            "book_student AS t03",
            "INNER JOIN book_order as t04 on t03.order_id = t04.id",
            "LEFT JOIN book_fetch AS t05 ON t05.order_id = t04.id  AND t05.school_id = t03.school_id  AND t05.subjects_id = t03.subjects_id AND t05.class_type_id = t03.class_type_id AND t05.year_class_id = t03.year_class_id",
            "LEFT JOIN student_grade AS t06 ON t06.year_part = t03.year_part ",
            "AND t06.quarter_num = t03.quarter_num ",
            "AND t06.school_id = t03.school_id ",
            "AND t06.subjects_id = t03.subjects_id",
            "AND t06.lesson_type = t03.lesson_type ",
            "AND t06.student_id = t03.student_id ",
//            "AND t06.reading_status IN (0,1) ",
            "AND t06.`status` = 1  ",
            "AND t06.`reading_status` &lt;&gt; 3 ",
            "AND t06.year_class_id = t03.year_class_id ",
//            "AND t04.class_type_id = ( SELECT course_type_id FROM grade WHERE grade.id = t06.grade_id LIMIT 1) ",
            "AND t06.is_last = 1 ",
            "AND t06.deleted = 0 ",
            "LEFT JOIN book_grant_info AS t07 ON t03.id = t07.book_student_id  ",
            "<if test='disType == 0 '>",
                "AND t07.`status` = 4 ",
            "</if>",
            "<if test='(disType == 2 and isDetails == 0) or (disType == 1)'>",
                "AND t07.`status` = 5 ",
            "</if>",
            "LEFT JOIN staff AS t08 ON t03.teacher_id = t08.id ",
            "<when test='subscribeType == 1 '>",
//                "INNER JOIN subscribe_student_tb AS t09 ON t09.student_id = t03.student_id ",
            "</when>",
            "WHERE",
            "t03.year_part = ${yearPart} ",
            "AND t03.quarter_num = ${quarterNum} ",
            "AND t03.lesson_type = ${lessonType} ",

            "<when test='subscribeType == 1 '>",
//                "AND t09.id IS NOT NULL ",
                "AND t03.lesson_num = ${lessonNum}",
            "</when>",

            "<if test='type == 2'>",
                "AND t03.school_id = ${schoolId} ",
            "</if>",

            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "AND t03.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                "AND t03.class_type_id = ${classTypeId}",
            "</when>",
            "<when test='(disType == 1 and isDetails == 0) or (disType == 0 and isDetails == 1) and teacherId!=null and teacherId!=&apos;&apos; '>",
                "AND t03.teacher_id = ${teacherId}",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                "AND t03.year_class_id IN (${yearClassId}) ",
            "</when>",
            "<when test='bookTextId!=null and bookTextId!=&apos;&apos; '>",
                "AND t03.book_text_id IN (${bookTextId})",
            "</when>",

            //1学生用书，2教师用书
            "AND t03.book_text_type = ${bookTextType}",

            "<if test='disType == 0'>",
            // 发放到校区
//                "<if test='orderSubType==null'>",
                    "AND t03.`status` >= 3 ",
//                "</if>",
//                "<if test='orderSubType!=null and orderSubType == 0'>",
//                    "AND t03.`status` = 3 ",
//                "</if>",
//                "<if test='orderSubType!=null and orderSubType == 1'>",
//                    "AND t03.`status` > 3 ",
//                "</if>",
            "</if>",

            "<if test='disType == 1'>",
            // 发放到教师（校区详情、教师列表）
//                "<if test='orderSubType==null'>",
                    "AND t03.`status` >= 4 ",
//                "</if>",
//                "<if test='orderSubType!=null and orderSubType == 0'>",
//                    "AND t03.`status` = 4 ",
//                "</if>",
//                "<if test='orderSubType!=null and orderSubType == 1'>",
//                    "AND t03.`status` > 4 ",
//                "</if>",
            "</if>",

            "<if test='orderSubType!=null and orderSubType == 0'>",
                "AND t07.id IS NULL",
            "</if>",
            "<if test='orderSubType!=null and orderSubType == 1'>",
                "AND t07.id IS NOT NULL",
            "</if>",

            "<when test='orderId!=null '>",
                "AND t04.id = ${orderId}",
            "</when>",

            "<when test='orderId!=null and orderType == 2'>",
                "AND t03.status &gt;= 2",
            "</when>",

            "GROUP BY",
            "t03.class_type_id,",
            "t03.year_class_id",
            "<if test='disType == 0'>",
                ",t03.school_id ",
                "<if test='isDetails == 1'>",
                    ",t03.teacher_id ",
                "</if>",
            "</if>",
            "<if test='disType == 1'>",
                ",t03.school_id,t03.teacher_id ",
                "<if test='isDetails == 1'>",
                ",t03.student_id ",
                "</if>",
            "</if>",
            "<if test='disType == 2'>",
                ",t03.school_id,t03.student_id ",
            "</if>",
            "</script>"})
    public abstract List<BookSchoolCountRs> getGrantCountList(
            @Param("yearPart") Integer yearPart,
            @Param("quarterNum") Integer quarterNum,
            @Param("schoolId") Long schoolId,
            @Param("subjectsId") String subjectsId,
            @Param("classTypeId") String classTypeId,
            @Param("yearClassId") String yearClassId,
            @Param("lessonType") Integer lessonType,
            @Param("orderId") Long orderId,
            @Param("type") Integer type,
            @Param("orderType") Integer orderType,
            @Param("orderSubType") Integer orderSubType,
            @Param("teacherId") Long teacherId,
            @Param("teacherName") String teacherName,
            @Param("disType") Integer disType,
            @Param("isDetails") Integer isDetails,
            @Param("bookTextId") Long bookTextId,
            @Param("bookTextType") Integer bookTextType,
            @Param("subscribeType") Integer subscribeType,
            @Param("lessonNum") Integer lessonNum,
            @Param("beforeLessonNum") Integer beforeLessonNum,
            @Param("rade") Integer rade
    );


    /**
     * 库存
     * @param page
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @param classTypeId
     * @param yearClassId
     * @param lessonType
     * @param schoolId
     * @param teacherName
     * @param studentName
     * @param gradeId
     * @param isDetails 0列表数据，1中间列表数据
     * @param type 0未发放，1已发放
     * @return
     */
    @Select({"<script>",
//            "<when test='subscribeType == 1 '>",
//                "WITH subscribe_student_tb AS (",
//                    "SELECT * FROM (",
//                    "SELECT ",
//                    "t01.id,",
//                    "t01.student_name,",
//                    "t01.student_id,",
//                    "t01.class_type_id,",
//                    "t01.year_class_id,",
//                    "t01.school_id,",
//                    "lowest_shutnum,",
//                    "IFNULL( t02.right_num, 0 ) AS right_num,",
//                    "IFNULL( t02.amend_num, 0 ) AS amend_num,",
//                    "CASE WHEN lowest_shutnum &gt; 0 THEN ROUND( IFNULL( right_num / lowest_shutnum, 0 ) * 100, 2 ) ELSE 0 END AS rate ",
//                    "FROM ",
//                    "math_db.per_student_account AS t01",
//                    "INNER JOIN (",
//                        "SELECT * FROM math_db.cha_plan ",
//                        "WHERE",
//                        "year_part = ${yearPart}",
//                        "AND quarter_num = ${quarterNum}",
//                        "AND task_category_id = 1 ",
//                        "AND deleted = 0 ",
//                        "AND lesson_num = ${beforeLessonNum}",
//                        "AND is_first = 1 ",
//                    ") AS t02 ON t01.student_id = t02.student_id ",
//                    "AND t01.class_type_id = t02.class_type_id ",
//                    "AND t01.year_class_id = t02.year_class_id ",
//                ") AS t ",
//                "WHERE rate &lt; ${rade} )",
//            "</when>",

            "SELECT * FROM ( ",
            "SELECT",
            "t01.id,",
            "t02.id AS order_id,",
            "t01.student_id,",
            "t02.subjects_id,",
            "<if test='isDetails != 5'>",
                "t04.`name` AS grade_name,",// 当前学生所在的班级
            "</if>",
            "<if test='isDetails == 5'>",
                "t08.`name` AS grade_name,",// 原本订购的班级
            "</if>",
            "t05.`name` AS student_name,",
            "t07.`name` AS teacher_name,",
            "t02.grant_date_time,",
            "t02.teacher_id,",
            "getSubjectsName( t02.subjects_id) as subjects_name,",
            "getCourseTypeName ( t02.class_type_id ) class_type_name,",
            "t02.class_type_id,",
            "getSchoolName ( t02.school_id ) AS school_name,",
            "t02.school_id,",
            "getDictName ( t02.year_class_id, 14 ) AS year_class_name, ",
            "t02.year_class_id,",
            "count( t02.id ) AS count ",
            "FROM ",
            "book_student AS t02 ",
            "LEFT JOIN student_grade AS t01 ON t01.student_id = t02.student_id ",
            "AND t01.year_part = t02.year_part ",
            "AND t01.quarter_num = t02.quarter_num ",
            "AND t01.subjects_id = t02.subjects_id ",
            "AND t01.lesson_type = t02.lesson_type ",
            "AND t01.year_class_id = t02.year_class_id ",
            "<if test='type == 0'>",
                "AND t01.reading_status IN ( 0, 1 ) ",
            "</if>",
            "AND t01.lesson_type = ${lessonType}",
            "AND t01.is_last = 1 ",
            "AND t01.deleted = 0 ",
            "LEFT JOIN grade AS t04 ON t01.grade_id = t04.id ",// 当前的班级
            "LEFT JOIN student AS t05 ON t01.student_id = t05.id ",
            "LEFT JOIN book_grant_info AS t06 ON t02.id = t06.book_student_id AND t06.`status` = 5 ",
            "INNER JOIN staff AS t07 ON t02.teacher_id = t07.id ",
            "LEFT JOIN grade AS t08 ON t02.grade_id = t08.id ", // 订购的时候的班级
            "<when test='subscribeType == 1 '>",
//                "INNER JOIN subscribe_student_tb AS t09 ON t09.student_id = t02.student_id ",
            "</when>",
            "WHERE 1=1",
            "AND t02.year_part = ${yearPart}",
            "AND t02.quarter_num = ${quarterNum}",
            "AND t02.school_id in(${schoolIdStr})",
            "AND t02.`status` &gt; 2 ",
            "<when test='subscribeType == 1 '>",
//                "AND t09.id IS NOT NULL ",
                "AND t02.lesson_num = ${lessonNum}",
            "</when>",
            "<when test='bookTextId!=null and bookTextId!=&apos;&apos; '>",
                "AND t02.book_text_id IN (${bookTextId})",
            "</when>",
            // 1学生用书，2教师用书
            "AND t02.book_text_type = ${bookTextType}",

            "<if test='type == 0'>",
            // 未发放
                "AND t06.id IS NULL",
                "AND t02.id IS NOT NULL ",
                "AND (( t04.course_type_id &lt;&gt; t02.class_type_id ) OR ( t01.year_class_id &lt;&gt; t02.year_class_id )) ",
            "</if>",

            "<if test='type == 1'>",
            // 已发放
                "AND t02.`status` = 5 ",
                "AND t02.type = 2 ",
            "</if>",

            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            // 目的：列表查询出年级不一致的学生，为库存数据，详情查询出年级一致的学生，为待发放数据
                "<if test='type == 0'>",
                    "AND t02.year_class_id = ${yearClassId} ",
                "</if>",
                "<if test='type == 1'>",
                    "AND t02.year_class_id = ${yearClassId} ",
                "</if>",
            "</when>",

            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            // 目的：列表查询出班型不一致的学生，为库存数据，详情查询出班型一致的学生，为待发放数据
                "<if test='type == 0'>",
                    "AND t02.class_type_id = ${classTypeId} ",
                "</if>",
                "<if test='type == 1'>",
                    "AND t02.class_type_id = ${classTypeId} ",
                "</if>",
            "</when>",

            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            // 目的：列表查询出校区不一致的学生，为库存数据，详情查询出校区一致的学生，为待发放数据
                "<if test='type == 0'>",
                    "AND t02.school_id IN (${schoolId}) ",
                "</if>",
                "<if test='type == 1'>",
                    "AND t02.school_id IN (${schoolId})",
                "</if>",
            "</when>",

            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "<if test='type == 0'>",
                    "AND t04.subjects_id = ${subjectsId}",
                "</if>",
                "<if test='type == 1'>",
                    "AND t02.subjects_id = ${subjectsId}",
                "</if>",
            "</when>",

            "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
                "<if test='isDetails != 5'>",
                    "AND t04.id = ${gradeId}",
                "</if>",
                "<if test='isDetails == 5'>",
                    "AND t08.id = ${gradeId}",
                "</if>",
            "</when>",

            "<when test='studentName!=null and studentName!=&apos;&apos; '>",
                "AND t05.`name` LIKE '%${studentName}%' ",
            "</when>",

            "<if test='isDetails == 5'>",
                "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                    "AND t02.teacher_id = ${teacherId}",
                "</when>",
            "</if>",

            "GROUP BY",
            "<if test='isDetails == 0'>",
                "t02.school_id",
            "</if>",
            "<if test='isDetails == 1'>",
                "t02.year_class_id",
            "</if>",
            "<if test='isDetails == 3'>",
                "t02.class_type_id,t02.subjects_id ",
            "</if>",
            "<if test='isDetails == 4'>",
                "t02.teacher_id ",
            "</if>",
            "<if test='isDetails == 5'>",
                "t02.student_id ",
            "</if>",
            "ORDER BY",
            "t02.year_class_id",
            ") as t",
            "WHERE 1=1",
            "<when test='idList!=null and idList!=&apos;&apos; '>",
                "AND id IN (${idList})",
            "</when>",
            "</script>"})
    public abstract IPage<BookSchoolRs> getRepertoryUnissuedList(IPage<?> page,
                                                                 @Param("yearPart") Integer yearPart,
                                                                 @Param("quarterNum") Integer quarterNum,
                                                                 @Param("subjectsId") String subjectsId,
                                                                 @Param("classTypeId") String classTypeId,
                                                                 @Param("yearClassId") String yearClassId,
                                                                 @Param("lessonType") Integer lessonType,
                                                                 @Param("schoolId") String schoolId,
                                                                 @Param("teacherName") String teacherName,
                                                                 @Param("studentName") String studentName,
                                                                 @Param("gradeId") Long gradeId,
                                                                 @Param("isDetails") Integer isDetails,
                                                                 @Param("type") Integer type,
                                                                 @Param("idList") String idList,
                                                                 @Param("schoolIdStr") String schoolIdStr,
                                                                 @Param("bookTextId") Long bookTextId,
                                                                 @Param("bookTextType") Integer bookTextType,
                                                                 @Param("teacherId") Long teacherId,
                                                                 @Param("subscribeType") Integer subscribeType,
                                                                 @Param("lessonNum") Integer lessonNum,
                                                                 @Param("beforeLessonNum") Integer beforeLessonNum,
                                                                 @Param("rade") Integer rade
    );

    @Select({"<script>",
//            "<when test='subscribeType == 1 '>",
//                "WITH subscribe_student_tb AS (",
//                    "SELECT * FROM (",
//                    "SELECT ",
//                    "t01.id,",
//                    "t01.student_name,",
//                    "t01.student_id,",
//                    "t01.class_type_id,",
//                    "t01.year_class_id,",
//                    "t01.school_id,",
//                    "lowest_shutnum,",
//                    "IFNULL( t02.right_num, 0 ) AS right_num,",
//                    "IFNULL( t02.amend_num, 0 ) AS amend_num,",
//                    "CASE WHEN lowest_shutnum &gt; 0 THEN ROUND( IFNULL( right_num / lowest_shutnum, 0 ) * 100, 2 ) ELSE 0 END AS rate ",
//                    "FROM ",
//                    "math_db.per_student_account AS t01",
//                    "INNER JOIN (",
//                        "SELECT * FROM math_db.cha_plan ",
//                        "WHERE",
//                        "year_part = ${yearPart}",
//                        "AND quarter_num = ${quarterNum}",
//                        "AND task_category_id = 1 ",
//                        "AND deleted = 0 ",
//                        "AND lesson_num = ${beforeLessonNum}",
//                        "AND is_first = 1 ",
//                    ") AS t02 ON t01.student_id = t02.student_id ",
//                    "AND t01.class_type_id = t02.class_type_id ",
//                    "AND t01.year_class_id = t02.year_class_id ",
//                ") AS t ",
//                "WHERE rate &lt; ${rade} )",
//            "</when>",

            "SELECT",
            "t01.id,",
            "t04.year_class_id,",
            "getDictName(t04.year_class_id, 14) as year_class_name,",
            "count( t02.id ) AS count ",

            "FROM ",
            "book_student AS t02 ",
            "LEFT JOIN student_grade AS t01 ON t01.student_id = t02.student_id ",
            "AND t01.year_part = t02.year_part ",
            "AND t01.quarter_num = t02.quarter_num ",
            "AND t01.subjects_id = t02.subjects_id ",
            "AND t01.lesson_type = t02.lesson_type ",
            "AND t01.year_class_id = t02.year_class_id ",
            "<if test='type == 0'>",
                "AND t01.reading_status IN ( 0, 1 ) ",
            "</if>",
            "AND t01.lesson_type = ${lessonType}",
            "AND t01.is_last = 1 ",
            "AND t01.deleted = 0 ",
            "LEFT JOIN grade AS t04 ON t01.grade_id = t04.id ",
            "LEFT JOIN student AS t05 ON t01.student_id = t05.id ",
            "LEFT JOIN book_grant_info AS t06 ON t02.id = t06.book_student_id AND t06.`status` = 5 ",
            "<when test='subscribeType == 1 '>",
//                "INNER JOIN subscribe_student_tb AS t07 ON t07.student_id = t02.student_id ",
            "</when>",
            "WHERE 1=1",
            "AND t02.year_part = ${yearPart}",
            "AND t02.quarter_num = ${quarterNum}",
            "AND t02.`status` &gt; 2 ",
            "<when test='subscribeType == 1 '>",
//                "AND t05.id IS NOT NULL ",
                "AND t02.lesson_num = ${lessonNum}",
            "</when>",
            "<when test='bookTextId!=null and bookTextId!=&apos;&apos; '>",
                "AND t02.book_text_id IN (${bookTextId})",
            "</when>",
            // 1学生用书，2教师用书
            "AND t02.book_text_type = ${bookTextType}",

            "<if test='type == 0'>",
                // 未发放
                "AND t06.id IS NULL ",
                "AND t02.id IS NOT NULL ",
                "AND (( t04.course_type_id &lt;&gt; t02.class_type_id ) OR ( t01.year_class_id &lt;&gt; t02.year_class_id )) ",
            "</if>",

            "<if test='type == 1'>",
                // 已发放
                "AND t02.id IS NOT NULL ",
                "AND t02.type = 2 ",
            "</if>",

            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                // 目的：列表查询出年级不一致的学生，为库存数据，详情查询出年级一致的学生，为待发放数据
                "<if test='type == 0 '>",
                    "AND t02.year_class_id = ${yearClassId} ",
                "</if>",
                "<if test='type == 1'>",
                    "AND t02.year_class_id = ${yearClassId} ",
                "</if>",
            "</when>",

            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                // 目的：列表查询出班型不一致的学生，为库存数据，详情查询出班型一致的学生，为待发放数据
                "<if test='type == 0 '>",
                    "AND t02.class_type_id = ${classTypeId} ",
                "</if>",
                "<if test='type == 1'>",
                    "AND t02.class_type_id = ${classTypeId} ",
                "</if>",
            "</when>",

            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                // 目的：列表查询出校区不一致的学生，为库存数据，详情查询出校区一致的学生，为待发放数据
                "<if test='type == 0 '>",
                    "AND t02.school_id = ${schoolId} ",
                "</if>",
                "<if test='type == 1 '>",
                    "AND t02.school_id = ${schoolId}",
                "</if>",
            "</when>",

            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "<if test='type == 0'>",
                    "AND t02.subjects_id = ${subjectsId}",
                "</if>",
                "<if test='type == 1'>",
                    "AND t02.subjects_id = ${subjectsId}",
                "</if>",
            "</when>",

            "<if test='isDetails == 4'>",
                "AND t02.teacher_id = ${teacherId}",
            "</if>",

            "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
                "AND t04.id = ${gradeId}",
            "</when>",

            "<when test='studentName!=null and studentName!=&apos;&apos; '>",
                "AND t05.`name` LIKE '%${studentName}%' ",
            "</when>",

            "GROUP BY",
            "t02.year_class_id",
            "<if test='isDetails == 4'>",
                ",t02.teacher_id ",
            "</if>",
            "ORDER BY",
            "t02.year_class_id",
            "</script>"})
    public abstract List<BookSchoolCountRs> getRepertoryUnissuedCountList(
            @Param("yearPart") Integer yearPart,
            @Param("quarterNum") Integer quarterNum,
            @Param("subjectsId") String subjectsId,
            @Param("classTypeId") String classTypeId,
            @Param("yearClassId") String yearClassId,
            @Param("lessonType") Integer lessonType,
            @Param("schoolId") Long schoolId,
            @Param("teacherName") String teacherName,
            @Param("studentName") String studentName,
            @Param("gradeId") Long gradeId,
            @Param("isDetails") Integer isDetails,
            @Param("type") Integer type,
            @Param("bookTextId") Long bookTextId,
            @Param("bookTextType") Integer bookTextType,
            @Param("teacherId") Long teacherId,
            @Param("subscribeType") Integer subscribeType,
            @Param("lessonNum") Integer lessonNum,
            @Param("beforeLessonNum") Integer beforeLessonNum,
            @Param("rade") Integer rade
    );


    /**
     * 库存详情
     * @param page
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @param classTypeId
     * @param yearClassId
     * @param lessonType
     * @param schoolId
     * @param teacherName
     * @param studentName
     * @param gradeId
     * @param isDetails
     * @param type
     * @param idList
     * @return
     */
    @Select({"<script>",
//            "<when test='subscribeType == 1 '>",
//                "WITH subscribe_student_tb AS (",
//                    "SELECT * FROM (",
//                        "SELECT ",
//                        "t01.id,",
//                        "t01.student_name,",
//                        "t01.student_id,",
//                        "t01.class_type_id,",
//                        "t01.year_class_id,",
//                        "t01.school_id,",
//                        "lowest_shutnum,",
//                        "IFNULL( t02.right_num, 0 ) AS right_num,",
//                        "IFNULL( t02.amend_num, 0 ) AS amend_num,",
//                        "CASE WHEN lowest_shutnum &gt; 0 THEN ROUND( IFNULL( right_num / lowest_shutnum, 0 ) * 100, 2 ) ELSE 0 END AS rate ",
//                        "FROM ",
//                        "math_db.per_student_account AS t01",
//                        "INNER JOIN (",
//                            "SELECT * FROM math_db.cha_plan ",
//                            "WHERE",
//                            "year_part = ${yearPart}",
//                            "AND quarter_num = ${quarterNum}",
//                            "AND task_category_id = 1 ",
//                            "AND deleted = 0 ",
//                            "AND lesson_num = ${beforeLessonNum}",
//                            "AND is_first = 1 ",
//                        ") AS t02 ON t01.student_id = t02.student_id ",
//                        "AND t01.class_type_id = t02.class_type_id ",
//                        "AND t01.year_class_id = t02.year_class_id ",
//                    ") AS t ",
//                "WHERE rate &lt; ${rade} )",
//            "</when>",

            "SELECT * FROM ( ",
            "SELECT",
            "t01.id,",
            "t02.id AS order_id,",
            "t01.student_id,",
            "t02.subjects_id,",
            "t04.`name` AS grade_name,",
            "t05.`name` AS student_name,",
            "t02.grant_date_time,",
            "getSubjectsName( t02.subjects_id) as subjects_name,",
            "getCourseTypeName ( t02.class_type_id ) class_type_name,",
            "t04.course_type_id AS class_type_id,",
            "getSchoolName ( t04.school_id ) AS school_name,",
            "t04.school_id,",
            "getDictName ( t04.year_class_id, 14 ) AS year_class_name, ",
            "t04.year_class_id,",
            "t04.id AS grade_id,",
            "t04.teacher_id,",
            "count( t02.id ) AS count ",
            "FROM ",
            "student_grade AS t01",
            "LEFT JOIN book_student AS t02 ON t01.student_id = t02.student_id ",
            "AND t01.year_part = t02.year_part ",
            "AND t01.quarter_num = t02.quarter_num ",
            "AND t01.subjects_id = t02.subjects_id ",
            "AND t01.lesson_type = t02.lesson_type ",
            "AND t01.year_class_id = t02.year_class_id ",
            "<when test='bookTextId!=null and bookTextId!=&apos;&apos; '>",
                "AND t02.book_text_id IN (${bookTextId})",
            "</when>",
            //1学生用书，2教师用书
            "AND t02.book_text_type = ${bookTextType}",
            "LEFT JOIN grade AS t04 ON t01.grade_id = t04.id ",
            "LEFT JOIN student AS t05 ON t01.student_id = t05.id ",
            "LEFT JOIN book_grant_info AS t06 ON t02.id = t06.book_student_id AND t06.`status` = 5 ",
            "<when test='subscribeType == 1 '>",
//                "INNER JOIN subscribe_student_tb AS t07 ON t07.student_id = t02.student_id ",
            "</when>",
            "WHERE 1=1",
            "<if test='type == 0'>",
                "AND t01.reading_status IN ( 0, 1 ) ",
            "</if>",
            "AND t01.lesson_type = ${lessonType}",
            "AND t01.school_id in(${schoolIdStr})",
            "AND t01.is_last = 1 ",
            "AND t01.deleted = 0 ",
            "<when test='subscribeType == 1 '>",
//                "AND t07.id IS NOT NULL ",
                "AND t02.lesson_num = ${lessonNum}",
            "</when>",

//            "AND t02.`status` &gt; 2 ",
            "<if test='type == 0'>",
                // 未发放，详情查询未订购的数据（待发放学生）
                "AND t06.id IS NULL ",
                "AND t02.id IS NULL",
                "AND t01.year_part = ${yearPart}",
                "AND t01.quarter_num = ${quarterNum}",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                    "AND t04.course_type_id = ${classTypeId} ",
                "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                    "AND t04.year_class_id = ${yearClassId} ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                    "AND t04.subjects_id = ${subjectsId}",
                "</when>",
                "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                    "AND t04.school_id IN (${schoolId})",
                "</when>",
            "</if>",

            "<if test='type == 1'>",
                // 已发放
                "AND t02.`status` = 5 ",
                "AND t02.type = 2 ",
                "AND t02.year_part = ${yearPart}",
                "AND t02.quarter_num = ${quarterNum}",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                    "AND t02.class_type_id = ${classTypeId} ",
                "</when>",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                    "AND t02.year_class_id = ${yearClassId} ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                    "AND t02.subjects_id = ${subjectsId}",
                "</when>",
                "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                    "AND t02.school_id IN (${schoolId})",
                "</when>",
            "</if>",

            "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
                "AND t04.id = ${gradeId}",
            "</when>",
            "<when test='studentName!=null and studentName!=&apos;&apos; '>",
                "AND t05.`name` LIKE '%${studentName}%' ",
            "</when>",

            "GROUP BY",
            "t01.student_id",
            "ORDER BY",
            "t04.teacher_id",
            ") as t",
            "WHERE 1=1",
            "<when test='idList!=null and idList!=&apos;&apos; '>",
            "AND id IN (${idList})",
            "</when>",
            "</script>"})
    public abstract IPage<BookSchoolRs> getCatIssuedList(IPage<?> page,
                                                         @Param("yearPart") Integer yearPart,
                                                         @Param("quarterNum") Integer quarterNum,
                                                         @Param("subjectsId") String subjectsId,
                                                         @Param("classTypeId") String classTypeId,
                                                         @Param("yearClassId") String yearClassId,
                                                         @Param("lessonType") Integer lessonType,
                                                         @Param("schoolId") String schoolId,
                                                         @Param("teacherName") String teacherName,
                                                         @Param("studentName") String studentName,
                                                         @Param("gradeId") Long gradeId,
                                                         @Param("isDetails") Integer isDetails,
                                                         @Param("type") Integer type,
                                                         @Param("idList") String idList,
                                                         @Param("schoolIdStr") String schoolIdStr,
                                                         @Param("bookTextId") Long bookTextId,
                                                         @Param("bookTextType") Integer bookTextType,
                                                         @Param("subscribeType") Integer subscribeType,
                                                         @Param("lessonNum") Integer lessonNum,
                                                         @Param("beforeLessonNum") Integer beforeLessonNum,
                                                         @Param("rade") Integer rade
    );


    @Select({"<script>",
            "SELECT",
            "t02.*",
            "FROM ",
            "student_grade AS t01 ",
            "LEFT JOIN book_student AS t02 ON t01.student_id = t02.student_id ",
            "AND t01.year_part = t02.year_part ",
            "AND t01.quarter_num = t02.quarter_num ",
            "AND t01.subjects_id = t02.subjects_id ",
            "AND t01.lesson_type = t02.lesson_type ",
            "LEFT JOIN grade AS t04 ON t01.grade_id = t04.id ",
            "LEFT JOIN student AS t05 ON t01.student_id = t05.id ",
            "LEFT JOIN book_grant_info AS t06 ON t02.id = t06.book_student_id AND t06.`status` = 5 ",
            "WHERE ",
            "t01.lesson_type = ${lessonType} ",
            "AND t01.reading_status IN ( 0, 1 ) ",
            "AND t01.is_last = 1 ",
            "AND t01.deleted = 0 ",
            "AND t02.id IS NOT NULL ",
            "AND t06.id IS NULL ",
            "AND t02.year_part = ${yearPart} ",
            "AND t02.quarter_num = ${quarterNum} ",
            "AND t02.year_class_id = ${yearClassId} ",
            "AND t02.class_type_id = ${classTypeId} ",
            "AND t02.school_id = ${schoolId} ",
            "AND t02.subjects_id = ${subjectsId}",
            "<when test='bookTextId!=null and bookTextId!=&apos;&apos; '>",
                "AND t02.book_text_id IN (${bookTextId})",
            "</when>",
            // 调整班型、年级、校区的学生
            "AND (( t04.course_type_id &lt;&gt; t02.class_type_id ) OR ( t01.year_class_id &lt;&gt; t02.year_class_id ) OR ( t01.school_id &lt;&gt; t02.school_id )) ",
            "GROUP BY",
            "t02.school_id,t01.student_id",
            "LIMIT 1 ",
            "</script>"})
    public abstract BookStudent getNotIssued(
            @Param("yearPart") Integer yearPart,
            @Param("quarterNum") Integer quarterNum,
            @Param("subjectsId") String subjectsId,
            @Param("classTypeId") String classTypeId,
            @Param("yearClassId") String yearClassId,
            @Param("lessonType") Integer lessonType,
            @Param("schoolId") Long schoolId,
            @Param("bookTextId") Long bookTextId

    );


    /**
     * 获取发放信息
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @param classTypeId
     * @param lessonType
     * @param orderType 1已订购，2下单，3领取，4发放
     * @return
     */
    @Select({"<script>",
            "select * ,IF( fetch_count &gt;= all_count, 1, 0 ) AS fetch_status,IF( order_count &gt;= all_count, 1, 0 ) AS order_status,IF( dis_count >= all_count, 1, 0 ) AS dis_status  from(",
            "SELECT",
            "t01.*,",
            "getSchoolName(t01.school_id) as school_name,",
            "getCourseTypeName(t01.class_type_id) class_type_name,",
            "getDictName(t01.year_class_id, 14) as year_class_name,",
            "getSubjectsName(t01.subjects_id) as subjects_name,",
            "count( t01.id ) AS count,",
            "t03.order_id,",
            "t04.batch_name,",
            //"<if test='orderType!=null and orderType == 3'>",
            "count(t01.id) as all_count,",
            "SUM(( CASE WHEN t05.id IS NULL THEN 0 ELSE 1 END )) AS fetch_count,",
            "SUM( ( CASE WHEN t03.`status` >=2 THEN 1 ELSE 0 END ) ) AS order_count,",
            "SUM( ( CASE WHEN t03.`status` >= 4 THEN 1 ELSE 0 END ) ) AS dis_count,",
            //"(CASE WHEN t05.id IS NULL THEN 0 ELSE 1 END ) AS fetch_status,",
            "MAX(t05.created_at) AS fetch_date_time,",
            //"</if>",
            "${status} as status_param,",
            "${orderType} as order_type,",
            "${orderSubType} as order_sub_type,",
            "${type} as type",
            "FROM",
            "(",
            "SELECT",
            "t1.id,",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.lesson_type,",
            "t1.year_part,",
            "t1.quarter_num,",
            "t2.year_class_id,",
            "t2.course_type_id AS class_type_id ",
            "FROM",
            "student_grade AS t1",
            "INNER JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and t2.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "and t2.course_type_id = ${classTypeId}",
            "</when>",
            "AND t1.`status` = 1 ",
            "AND t1.reading_status IN ( 0, 1 ) ",
            "AND t1.lesson_type = ${lessonType} ",
            "GROUP BY",
            "student_id,",
            "subjects_id ",
            ") AS t01",
            "LEFT JOIN book_student AS t03 ON t01.year_part = t03.year_part ",
            "AND t01.quarter_num = t03.quarter_num ",
            "AND t01.student_id = t03.student_id ",
            "AND t01.subjects_id = t03.subjects_id ",
            "AND t01.lesson_type = t03.lesson_type ",
            "AND t01.class_type_id = t03.class_type_id ",
            "INNER JOIN book_order as t04 on t03.order_id = t04.id",
            "LEFT JOIN book_fetch AS t05 ON t05.order_id = t04.id  AND t05.school_id = t03.school_id  AND t05.subjects_id = t03.subjects_id AND t05.class_type_id = t03.class_type_id",
            "WHERE",
            "1=1",
            "<if test='orderType!=null and orderType == 2'>",
            //原先
//                "and t04.status = 2",
            "and t04.is_order_status = 1",
            "</if>",
            "<if test='orderType!=null and orderType == 3'>",
            //原来领取
//                "and t04.status in(2,3)",
            "and t04.is_order_status = 1",
            "and t03.status &gt;= 2 ",
            "<if test='orderSubType!=null and orderSubType == 1'>",
            "and t05.id is not null",
            "</if>",
            "<if test='orderSubType!=null and orderSubType == 0'>",
            "and t05.id is null",
            "</if>",
            "</if>",
            "<if test='orderType!=null and orderType == 4'>",
            "and t04.status in(3)",
            "<if test='orderSubType!=null and orderSubType == 1'>",
            "and t03.status = 4",
            "</if>",
            "<if test='orderSubType!=null and orderSubType == 0'>",
            "and t03.status &lt;&gt; 4",
            "</if>",
            "</if>",
            "<when test='orderId!=null'>",
            "and t04.id = ${orderId}",
            "</when>",
            //"<when test='status!=null'>",
            //"and t04.status = ${status}",
            //"</when>",
            "GROUP BY",
            "<if test='type  == 1'>",
            "t04.id",
            "</if>",
            "<if test='type  == 2'>",
            "t04.id,",
            "t01.school_id,",
            "t01.class_type_id",
            "</if>",
            ") as t",
            "</script>"})
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "bookCountList",
                    column = "{yearPart=year_part ,quarterNum=quarter_num,schoolId=school_id,subjectsId=subjects_id,classTypeId=class_type_id,lessonType=lesson_type,orderId=order_id,status=status_param,type=type,orderType=order_type,orderSubType=order_sub_type}",
                    many = @Many(select = "com.xmy.cultivate.mapper.BookOrderMapper.getYetSubCountList")
            )
    })
    public abstract List<BookSchoolRs> getDistributeOrderSchool1(@Param("orderId") Long orderId,
                                                         @Param("yearPart") Integer yearPart,
                                                         @Param("quarterNum") Integer quarterNum,
                                                         @Param("subjectsId") String subjectsId,
                                                         @Param("classTypeId") String classTypeId,
                                                         @Param("lessonType") Integer lessonType,
                                                         @Param("type") Integer type,
                                                         @Param("status") Integer status,
                                                         @Param("orderType") Integer orderType,
                                                         @Param("orderSubType") Integer orderSubType

    );



    //修改
    @Select({"<script>",
//            "<when test='subscribeType == 1 '>",
//                "WITH subscribe_student_tb AS (",
//                    "SELECT * FROM (",
//                    "SELECT ",
//                    "t01.id,",
//                    "t01.student_name,",
//                    "t01.student_id,",
//                    "t01.class_type_id,",
//                    "t01.year_class_id,",
//                    "t01.school_id,",
//                    "lowest_shutnum,",
//                    "IFNULL( t02.right_num, 0 ) AS right_num,",
//                    "IFNULL( t02.amend_num, 0 ) AS amend_num,",
//                    "CASE WHEN lowest_shutnum &gt; 0 THEN ROUND( IFNULL( right_num / lowest_shutnum, 0 ) * 100, 2 ) ELSE 0 END AS rate ",
//                    "FROM ",
//                    "math_db.per_student_account AS t01",
//                    "INNER JOIN (",
//                        "SELECT * FROM math_db.cha_plan ",
//                        "WHERE",
//                        "year_part = ${yearPart}",
//                        "AND quarter_num = ${quarterNum}",
//                        "AND task_category_id = 1 ",
//                        "AND deleted = 0 ",
//                        "AND lesson_num = ${beforeLessonNum}",
//                        "AND is_first = 1 ",
//                    ") AS t02 ON t01.student_id = t02.student_id ",
//                    "AND t01.class_type_id = t02.class_type_id ",
//                    "AND t01.year_class_id = t02.year_class_id ",
//                ") AS t ",
//                "WHERE rate &lt; ${rade} )",
//            "</when>",

            "select * ,IF( fetch_count &gt;= all_count, 1, 0 ) AS fetch_status,IF( order_count &gt;= all_count, 1, 0 ) AS order_status from(",
            "SELECT",
            "t03.year_part,",
            "t03.quarter_num,",
            "t03.school_id,",
            "t03.class_type_id,",
            "t03.year_class_id,",
            "t03.subjects_id,",
            "t03.lesson_type,",
            "getSchoolName(t03.school_id) as school_name,",
            "getCourseTypeName(t03.class_type_id) class_type_name,",
            "getDictName(t03.year_class_id, 14) as year_class_name,",
            "getSubjectsName(t03.subjects_id) as subjects_name,",
            "count( t03.id ) AS count,",
            "t03.order_id,",
            "t04.batch_name,",
            //"<if test='orderType!=null and orderType == 3'>",
            "count(t03.id) as all_count,",
            "SUM(( CASE WHEN t05.id IS NULL THEN 0 ELSE 1 END )) AS fetch_count,",
            "SUM( ( CASE WHEN t03.`status` >=2 THEN 1 ELSE 0 END ) ) AS order_count,",
            //"(CASE WHEN t05.id IS NULL THEN 0 ELSE 1 END ) AS fetch_status,",
            "MAX(t05.created_at) AS fetch_date_time,",
            //"</if>",
            "${status} as status_param,",
            "${orderType} as order_type,",
            "${orderSubType} as order_sub_type,",
            "${type} as type,",
            "${subscribeType} as subscribe_type,",
            "${lessonNum} as lesson_num,",
            "${beforeLessonNum} as before_lesson_num,",
            "${rade} as rade ",
            "FROM",
            "book_student AS t03",
            "INNER JOIN book_order as t04 on t03.order_id = t04.id",
            "LEFT JOIN book_fetch AS t05 ON t05.order_id = t04.id  AND t05.school_id = t03.school_id  AND t05.subjects_id = t03.subjects_id AND t05.class_type_id = t03.class_type_id AND t05.year_class_id = t03.year_class_id ",
            "<when test='subscribeType == 1 '>",
//                "INNER JOIN subscribe_student_tb AS t06 ON t06.student_id = t03.student_id ",
            "</when>",
            "WHERE",
            "t03.year_part = ${yearPart} ",
            "AND t03.quarter_num = ${quarterNum} ",
            "AND t03.school_id in(${schoolIdStr}) ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and t03.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "and t03.class_type_id = ${classTypeId}",
            "</when>",
            "AND t03.lesson_type = ${lessonType} ",
            "<when test='subscribeType == 1 '>",
//                "AND t06.id IS NOT NULL ",
                "AND t03.lesson_num = ${lessonNum}",
            "</when>",
            "<if test='orderType!=null and orderType == 2'>",
            //下单
//                "and t04.status = 2",
            "and t04.is_order_status = 1",
            "</if>",
            "<if test='orderType!=null and orderType == 3'>",
            "and t04.status in(2,3)",
            "<if test='orderSubType!=null and orderSubType == 1'>",
            "and t05.id is not null",
            "</if>",
            "<if test='orderSubType!=null and orderSubType == 0'>",
            "and t05.id is null",
            "</if>",
            "</if>",
            "<if test='orderType!=null and orderType == 4'>",
            "and t04.status in(3)",
            "<if test='orderSubType!=null and orderSubType == 1'>",
            "and t03.status = 4",
            "</if>",
            "<if test='orderSubType!=null and orderSubType == 0'>",
            "and t03.status &lt;&gt; 4",
            "</if>",
            "</if>",
            "<when test='orderId!=null'>",
            "and t04.id = ${orderId}",
            "AND t03.`status` &gt;= 2",
            "</when>",

       /*     //1学生用书，2教师用书
            "<when test='bookTextType!=null and bookTextType!=&apos;&apos; '>",
            "and t03.book_text_type = ${bookTextType}",
            "and t04.book_text_type = ${bookTextType}",
            "</when>",*/


            //"<when test='status!=null'>",
            //"and t04.status = ${status}",
            //"</when>",
            "GROUP BY",
            "<if test='type  == 1'>",
            "t04.id",
            "</if>",
            "<if test='type  == 2'>",
            "t04.id,",
            "t03.school_id,",
            "t03.class_type_id",
            "</if>",
            ") as t",
            "</script>"})
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "bookCountList",
                    column = "{yearPart=year_part ,quarterNum=quarter_num,schoolId=school_id,subjectsId=subjects_id,classTypeId=class_type_id,lessonType=lesson_type,orderId=order_id,status=status_param,type=type,orderType=order_type,orderSubType=order_sub_type,subscribeType=subscribe_type,lessonNum=lesson_num,beforeLessonNum=before_lesson_num,rade=rade}",
                    many = @Many(select = "com.xmy.cultivate.mapper.BookOrderMapper.getYetSubCountList")
            )
    })
    public abstract IPage<BookSchoolRs> getYetOrderSchoolDetail(IPage<?> page,
                                                                @Param("orderId") Long orderId,
                                                                @Param("yearPart") Integer yearPart,
                                                                @Param("quarterNum") Integer quarterNum,
                                                                @Param("subjectsId") String subjectsId,
                                                                @Param("classTypeId") String classTypeId,
                                                                @Param("lessonType") Integer lessonType,
                                                                @Param("type") Integer type,
                                                                @Param("status") Integer status,
                                                                @Param("orderType") Integer orderType,
                                                                @Param("orderSubType") Integer orderSubType,
                                                                @Param("schoolIdStr") String schoolIdStr,
                                                                @Param("subscribeType") Integer subscribeType,
                                                                @Param("lessonNum") Integer lessonNum,
                                                                @Param("beforeLessonNum") Integer beforeLessonNum,
                                                                @Param("rade") Integer rade
    );


    @Select({"<script>",
//            "<when test='subscribeType == 1 '>",
//                "WITH subscribe_student_tb AS (",
//                    "SELECT * FROM (",
//                        "SELECT ",
//                        "t01.id,",
//                        "t01.student_name,",
//                        "t01.student_id,",
//                        "t01.class_type_id,",
//                        "t01.year_class_id,",
//                        "t01.school_id,",
//                        "lowest_shutnum,",
//                        "IFNULL( t02.right_num, 0 ) AS right_num,",
//                        "IFNULL( t02.amend_num, 0 ) AS amend_num,",
//                        "CASE WHEN lowest_shutnum &gt; 0 THEN ROUND( IFNULL( right_num / lowest_shutnum, 0 ) * 100, 2 ) ELSE 0 END AS rate ",
//                        "FROM ",
//                        "math_db.per_student_account AS t01",
//                        "INNER JOIN (",
//                            "SELECT * FROM math_db.cha_plan ",
//                            "WHERE",
//                            "year_part = ${yearPart}",
//                            "AND quarter_num = ${quarterNum}",
//                            "AND task_category_id = 1 ",
//                            "AND deleted = 0 ",
//                            "AND lesson_num = ${beforeLessonNum}",
//                            "AND is_first = 1 ",
//                        ") AS t02 ON t01.student_id = t02.student_id ",
//                    "AND t01.class_type_id = t02.class_type_id ",
//                    "AND t01.year_class_id = t02.year_class_id ",
//                ") AS t ",
//                "WHERE rate &lt; ${rade} )",
//            "</when>",

            "SELECT",
            "t03.id,",
            "t03.school_id,",
            "t03.student_id,",
            "t03.subjects_id,",
            "t03.lesson_type,",
            "t03.year_part,",
            "t03.quarter_num,",
            "t03.year_class_id,",
            "t03.class_type_id,",
            "getDictName(t03.year_class_id, 14) as year_class_name,",
            "count( t03.id ) AS count ",
            "FROM",
            "book_student AS t03",
            "INNER JOIN book_order as t04 on t03.order_id = t04.id",
            "<when test='subscribeType == 1 '>",
//                "INNER JOIN subscribe_student_tb AS t05 ON t05.student_id = t03.student_id ",
            "</when>",
            "WHERE",
            "t03.year_part = ${yearPart} ",
            "AND t03.quarter_num = ${quarterNum} ",
            "AND t03.lesson_type = ${lessonType} ",
            "<when test='subscribeType == 1 '>",
//                "AND t05.id IS NOT NULL ",
                "AND t03.lesson_num = ${lessonNum}",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "<if test='type == 2 or isDetails == 1'>",
                    "AND t03.school_id = ${schoolId} ",
                "</if>",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "and t03.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                "and t03.class_type_id = ${classTypeId}",
            "</when>",
            "<when test='yearClassId!=null'>",// 避免幼儿园无法查询到
                "and t03.year_class_id = ${yearClassId}",
            "</when>",
            "<if test='orderType!=null and orderType == 2'>",
                "and t04.is_order_status = 1",
            "</if>",
            "<when test='bookTextId!=null and bookTextId!=&apos;&apos; '>",
                "and t03.book_text_id IN ( ${bookTextId} )",
            "</when>",
            "<when test='isDetails == 1'>",
                "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                    "and t03.teacher_id = ${teacherId} ",
                "</when>",
            "</when>",
            "<if test='orderType!=null and orderType == 3'>",
                "and t04.is_order_status = 1",
                "and t03.status &gt;= 2",
                "<if test='orderSubType!=null and orderSubType == 1'>",
                    "and t05.id is not null",
                "</if>",
                "<if test='orderSubType!=null and orderSubType == 0'>",
                    "and t05.id is null",
                "</if>",
            "</if>",
            "<if test='orderType!=null and orderType == 4'>",
                "and t04.status in(3)",
                "<if test='orderSubType!=null and orderSubType == 1'>",
                    "and t03.status = 4",
                "</if>",
                "<if test='orderSubType!=null and orderSubType == 0'>",
                    "and t03.status &lt;&gt; 4",
                "</if>",
            "</if>",
            "<when test='orderId!=null and orderType == 1'>",
                "and t04.id = ${orderId}",
            "</when>",
            "<when test='orderId!=null and orderType == 2'>",
                "and t04.id = ${orderId}",
            "</when>",
            "<when test='orderId!=null and orderType == 3'>",
                "and t04.id = ${orderId}",
            "</when>",
            //1学生用书，2教师用书
            "<when test='bookTextType!=null and bookTextType!=&apos;&apos; '>",
                "AND t03.book_text_type = ${bookTextType}",
            "</when>",
            "GROUP BY",
            "t04.id,",
            "<if test='type == 2'>",
                "t03.school_id,",
            "</if>",
            "t03.class_type_id,",
            "t03.year_class_id",
            "<when test='isDetails!=null and subjectsId!=&apos;&apos; '>",
                "<when test='isDetails == 1'>",
                    ",t03.teacher_id ",
                "</when>",
            "</when>",
            "</script>"})
    public abstract List<BookSchoolCountRs> getYetSubCountList(
            @Param("yearPart") Integer yearPart,
            @Param("quarterNum") Integer quarterNum,
            @Param("schoolId") Long schoolId,
            @Param("subjectsId") String subjectsId,
            @Param("classTypeId") String classTypeId,
            @Param("lessonType") Integer lessonType,
            @Param("orderId") Long orderId,
            @Param("status") Integer status,
            @Param("type") Integer type,
            @Param("orderType") Integer orderType,
            @Param("orderSubType") Integer orderSubType,
            @Param("isDetails") Integer isDetails,
            @Param("bookTextId") Long bookTextId,
            @Param("teacherId") Long teacherId,
            @Param("yearClassId") Integer yearClassId,
            @Param("bookTextType") Integer bookTextType,
            @Param("subscribeType") Integer subscribeType,
            @Param("lessonNum") Integer lessonNum,
            @Param("beforeLessonNum") Integer beforeLessonNum,
            @Param("rade") Integer rade
    );

    @Select({"<script>",
//            "<when test='subscribeType == 1 '>",
//                "WITH subscribe_student_tb AS (",
//                    "SELECT * FROM (",
//                        "SELECT ",
//                        "t01.id,",
//                        "t01.student_name,",
//                        "t01.student_id,",
//                        "t01.class_type_id,",
//                        "t01.year_class_id,",
//                        "t01.school_id,",
//                        "t03.rade,",
//                        "t02.subjects_id,",
//                        "lowest_shutnum,",
//                        "IFNULL( t02.right_num, 0 ) AS right_num,",
//                        "IFNULL( t02.amend_num, 0 ) AS amend_num,",
//                        "CASE WHEN lowest_shutnum &gt; 0 THEN ROUND( IFNULL( right_num / lowest_shutnum, 0 ) * 100, 2 ) ELSE 0 END AS rate ",
//                        "FROM ",
//                        "math_db.per_student_account AS t01",
//                        "INNER JOIN (",
//                            "SELECT * FROM math_db.cha_plan ",
//                            "WHERE",
//                            "year_part = ${yearPart}",
//                            "AND quarter_num = ${quarterNum}",
//                            "AND task_category_id = 1 ",
//                            "AND deleted = 0 ",
//                            "AND lesson_num = ${beforeLessonNum}",
//                            "AND is_first = 1 ",
//                        ") AS t02 ON t01.student_id = t02.student_id ",
//                        "AND t01.class_type_id = t02.class_type_id ",
//                        "AND t01.year_class_id = t02.year_class_id ",
//                        "LEFT JOIN xmy_erp_db.book_accuracy_set AS t03 ON t03.class_type_id = t01.class_type_id ",
//                        "AND t03.subjects_id = t02.subjects_id ",
//                    ") AS t ",
//                "WHERE rate &lt; rade )",
//            "</when>",

            "SELECT",
            "t03.id,",
            "t03.school_id,",
            "t03.student_id,",
            "t03.subjects_id,",
            "t03.lesson_type,",
            "t03.year_part,",
            "t03.quarter_num,",
            "t03.year_class_id,",
            "t03.class_type_id,",
            "getSchoolName(t03.school_id) as school_name,",
            "getCourseTypeName(t03.class_type_id) class_type_name,",
            "getDictName(t03.year_class_id, 14) as year_class_name,",
            "getSubjectsName(t03.subjects_id) as subjects_name,",
            "count( t03.id ) AS count,",
            "t03.order_id,",
            "t04.batch_name,",
            "(CASE WHEN t05.id IS NULL THEN 0 ELSE 1 END ) AS fetch_status ",
            "FROM",
            "book_student AS t03",
            "INNER JOIN book_order as t04 on t03.order_id = t04.id",
            "LEFT JOIN book_fetch AS t05 ON t05.order_id = t04.id  AND t05.school_id = t03.school_id  AND t05.subjects_id = t03.subjects_id AND t05.class_type_id = t03.class_type_id and t05.year_class_id = t03.year_class_id",
            "<when test='subscribeType == 1 '>",
//                "INNER JOIN subscribe_student_tb AS t06 ON t06.student_id = t03.student_id AND t03.subjects_id = t06.subjects_id AND t03.class_type_id = t06.class_type_id ",
            "</when>",
            "WHERE",
            "t03.year_part = ${yearPart} ",
            "AND t03.quarter_num = ${quarterNum} ",
            "<when test='subscribeType == 1 '>",
//                "AND t06.id IS NOT NULL ",
                "AND t03.lesson_num = ${lessonNum}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and t03.subjects_id = ${subjectsId}",
            "</when>",
            "AND t03.lesson_type = ${lessonType} ",
            "<when test='bookTextId!=null and bookTextId!=&apos;&apos; '>",
                "and t03.book_text_id IN ( ${bookTextId} )",
            "</when>",
            //1学生用书，2教师用书
            "and t03.book_text_type = ${bookTextType} ",

            "<if test='orderType!=null and orderType == 2'>",
            //下单
//            "and t04.status = 2",
            "and t04.is_order_status = 1",
//            "and t03.status = 2",
            "and t03.status &gt;= 2",
            "</if>",

            "<if test='orderType!=null and orderType == 3'>",
            //领取
//            "and t04.status in(2,3)",
            "and t04.is_order_status = 1",
            "and t03.status &gt;= 2",
            "<if test='orderSubType!=null and orderSubType == 1'>",
            "and t05.id is not null",
            "</if>",
            "<if test='orderSubType!=null and orderSubType == 0'>",
            "and t05.id is null",
            "</if>",
            "</if>",
            "<if test='orderType!=null and orderType == 4'>",
            "and t04.status in(3)",
            "<if test='orderSubType!=null and orderSubType == 1'>",
            "and t03.status = 4",
            "</if>",
            "<if test='orderSubType!=null and orderSubType == 0'>",
            "and t03.status &lt;&gt; 4",
            "</if>",
            "</if>",

            "<if test='orderType!=null and orderType == 4'>",
            // 发放

            "</if>",

            "<if test='orderType!=null and orderType == 5'>",
            // 库存

            "</if>",

            //"<when test='status!=null'>",
            //"and t04.status = ${status}",
            //"</when>",
            "GROUP BY",
            "t03.subjects_id,t03.class_type_id",
            "</script>"})
    public abstract List<BookClassTypeCountRs> getBookCountForClassType(@Param("yearPart") Integer yearPart,
                                                                        @Param("quarterNum") Integer quarterNum,
                                                                        @Param("subjectsId") String subjectsId,
                                                                        @Param("lessonType") Integer lessonType,
                                                                        @Param("status") Integer status,
                                                                        @Param("orderType") Integer orderType,
                                                                        @Param("orderSubType") Integer orderSubType,
                                                                        @Param("bookTextId") Long bookTextId,
                                                                        @Param("bookTextType") Integer bookTextType,
                                                                        @Param("subscribeType") Integer subscribeType,
                                                                        @Param("lessonNum") Integer lessonNum,
                                                                        @Param("beforeLessonNum") Integer beforeLessonNum
                                                                        );


    /**
     * 未下单
     */
    @Select({"<script>",
            "<when test='subscribeType == 1 '>",
                "WITH subscribe_student_tb AS (",
                    "SELECT * FROM (",
                        "SELECT ",
                        "t01.id,",
                        "t01.student_name,",
                        "t01.student_id,",
                        "t01.class_type_id,",
                        "t01.year_class_id,",
                        "t01.school_id,",
                        "lowest_shutnum,",
                        "IFNULL( t02.right_num, 0 ) AS right_num,",
                        "IFNULL( t02.amend_num, 0 ) AS amend_num,",
                        "CASE WHEN lowest_shutnum &gt; 0 THEN ROUND( IFNULL( right_num / lowest_shutnum, 0 ) * 100, 2 ) ELSE 0 END AS rate ",
                        "FROM ",
                        "<if test='subjectsId == 2'>",
                            "math_db.per_student_account AS t01",
                        "</if>",
                        "<if test='subjectsId == 3'>",
                            "physics_db.per_student_account AS t01",
                        "</if>",
                        "INNER JOIN (",
                            "SELECT * FROM ",
                            "<if test='subjectsId == 2'>",
                                "math_db.cha_plan",
                            "</if>",
                            "<if test='subjectsId == 3'>",
                                "physics_db.cha_plan",
                            "</if>",
                            "WHERE",
                            "year_part = ${yearPart}",
                            "AND quarter_num = ${quarterNum}",
                            "AND task_category_id = 1 ",
                            "AND deleted = 0 ",
                            "AND lesson_num = ${beforeLessonNum}",
                            "AND is_first = 1 ",
                        ") AS t02 ON t01.student_id = t02.student_id ",
                    "AND t01.class_type_id = t02.class_type_id ",
                    "AND t01.year_class_id = t02.year_class_id ",
                ") AS t ",
                "WHERE rate &lt; ${rade} )",
            "</when>",

            "select *  from(",
            "SELECT",
            "t03.year_part,",
            "t03.quarter_num,",
            "t03.school_id,",
            "t03.class_type_id,",
            "t03.year_class_id,",
            "t03.subjects_id,",
            "t03.lesson_type,",
            "t03.teacher_id,",
            "getSchoolName(t03.school_id) as school_name,",
            "getCourseTypeName(t03.class_type_id) class_type_name,",
            "getDictName(t03.year_class_id, 14) as year_class_name,",
            "getSubjectsName(t03.subjects_id) as subjects_name,",
            "t05.`name` AS teacher_name,",
            "t04.`name` AS student_name,",
            "t06.`name` AS grade_name,",
            "t03.order_id,",
            "count( t03.id ) AS count,",
            "${status} as status_param,",
            "${orderType} as order_type,",
            "${orderSubType} as order_sub_type,",
            "${type} as type",
            "FROM",
            "book_student AS t03",
            "INNER JOIN student AS t04 ON t03.student_id = t04.id ",
            "INNER JOIN staff AS t05 ON t03.teacher_id = t05.id ",
            "INNER JOIN grade AS t06 ON t03.grade_id = t06.id ",
            "<when test='subscribeType == 1 '>",
                "INNER JOIN subscribe_student_tb AS t07 ON t07.student_id = t03.student_id ",
            "</when>",
            "WHERE",
            "t03.year_part = ${yearPart} ",
            "AND t03.quarter_num = ${quarterNum} ",
            "AND t03.school_id in (${schoolIdStr}) ",
            "AND t03.`status` = 1 ",
            "<when test='subscribeType == 1 '>",
                "AND t07.id IS NOT NULL ",
                "AND t03.lesson_num = ${lessonNum}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "and t03.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                "and t03.class_type_id = ${classTypeId}",
            "</when>",
            "<when test='bookTextId!=null and bookTextId!=&apos;&apos; '>",
                "and t03.book_text_id IN ( ${bookTextId} )",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                "and t03.teacher_id = ${teacherId} ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "and t03.school_id = ${schoolId} ",
            "</when>",
            "<when test='yearClassId!=null'>",// 避免幼儿园无法查询到
                "and t03.year_class_id = ${yearClassId} ",
            "</when>",
            "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                "AND t05.`name` LIKE '%${teacherName}%' ",
            "</when>",
            "<when test='studentName!=null and studentName!=&apos;&apos; '>",
                "AND t04.`name` LIKE '%${studentName}%' ",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
                "AND t03.grade_id = ${gradeId} ",
            "</when>",
            //1学生用书，2教师用书
            "and t03.book_text_type = ${bookTextType}",

            "GROUP BY",
                "t03.school_id,",
                "t03.class_type_id",
            "<when test='isDetails == 1'>",
                ",t03.teacher_id ",
            "</when>",
            "<when test='isDetails == 2'>",
                ",t03.student_id ",
            "</when>",
            ") as t",
            "</script>"})
    public abstract IPage<BookSchoolRs> getNotOrderSchoolList(IPage<?> page,
                                                              @Param("yearPart") Integer yearPart,
                                                              @Param("quarterNum") Integer quarterNum,
                                                              @Param("subjectsId") String subjectsId,
                                                              @Param("classTypeId") String classTypeId,
                                                              @Param("lessonType") Integer lessonType,
                                                              @Param("type") Integer type,
                                                              @Param("status") Integer status,
                                                              @Param("orderType") Integer orderType,
                                                              @Param("orderSubType") Integer orderSubType,
                                                              @Param("schoolIdStr") String schoolIdStr,
                                                              @Param("bookTextId") Long bookTextId,
                                                              @Param("bookTextType") Integer bookTextType,
                                                              @Param("isDetails") Integer isDetails,
                                                              @Param("teacherId") Long teacherId,
                                                              @Param("schoolId") Long schoolId,
                                                              @Param("yearClassId") Integer yearClassId,
                                                              @Param("teacherName") String teacherName,
                                                              @Param("studentName") String studentName,
                                                              @Param("gradeId") Long gradeId,
                                                              @Param("subscribeType") Integer subscribeType,
                                                              @Param("lessonNum") Integer lessonNum,
                                                              @Param("beforeLessonNum") Integer beforeLessonNum,
                                                              @Param("rade") Integer rade
    );

    @Select({"<script>",
            "<when test='subscribeType == 1 '>",
                "WITH subscribe_student_tb AS (",
                    "SELECT * FROM (",
                        "SELECT ",
                        "t01.id,",
                        "t01.student_name,",
                        "t01.student_id,",
                        "t01.class_type_id,",
                        "t01.year_class_id,",
                        "t01.school_id,",
                        "lowest_shutnum,",
                        "IFNULL( t02.right_num, 0 ) AS right_num,",
                        "IFNULL( t02.amend_num, 0 ) AS amend_num,",
                        "CASE WHEN lowest_shutnum &gt; 0 THEN ROUND( IFNULL( right_num / lowest_shutnum, 0 ) * 100, 2 ) ELSE 0 END AS rate ",
                        "FROM ",
                        "<if test='subjectsId == 2'>",
                            "math_db.per_student_account AS t01",
                        "</if>",
                        "<if test='subjectsId == 3'>",
                            "physics_db.per_student_account AS t01",
                        "</if>",
                        "INNER JOIN (",
                            "SELECT * FROM ",
                            "<if test='subjectsId == 2'>",
                                "math_db.cha_plan",
                            "</if>",
                            "<if test='subjectsId == 3'>",
                                "physics_db.cha_plan",
                            "</if>",
                            "WHERE",
                            "year_part = ${yearPart}",
                            "AND quarter_num = ${quarterNum}",
                            "AND task_category_id = 1 ",
                            "AND deleted = 0 ",
                            "AND lesson_num = ${beforeLessonNum}",
                            "AND is_first = 1 ",
                        ") AS t02 ON t01.student_id = t02.student_id ",
                        "AND t01.class_type_id = t02.class_type_id ",
                        "AND t01.year_class_id = t02.year_class_id ",
                    ") AS t ",
                "WHERE rate &lt; ${rade} )",
            "</when>",

            "SELECT",
            "t03.id,",
            "t03.school_id,",
            "t03.student_id,",
            "t03.subjects_id,",
            "t03.lesson_type,",
            "t03.year_part,",
            "t03.quarter_num,",
            "t03.year_class_id,",
            "t03.class_type_id,",
            "getDictName(t03.year_class_id, 14) as year_class_name,",
            "count( t03.id ) AS count ",
            "FROM",
            "book_student AS t03",
            "<when test='subscribeType == 1 '>",
                "INNER JOIN subscribe_student_tb AS t05 ON t05.student_id = t03.student_id ",
            "</when>",
            "WHERE",
            "t03.year_part = ${yearPart} ",
            "AND t03.quarter_num = ${quarterNum} ",
            "AND t03.`status` = 1 ",
            "<when test='subscribeType == 1 '>",
                "AND t05.id IS NOT NULL ",
                "AND t03.lesson_num = ${lessonNum}",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "AND t03.school_id = ${schoolId} ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "and t03.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                "and t03.class_type_id = ${classTypeId}",
            "</when>",
            "<when test='isDetails == 1'>",
                "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                    "and t03.teacher_id = ${teacherId} ",
                "</when>",
            "</when>",
            "<when test='yearClassId!=null'>",// 避免幼儿园无法查询到
                "and t03.year_class_id = ${yearClassId} ",
            "</when>",
            "AND t03.lesson_type = ${lessonType} ",
            //1学生用书，2教师用书
            "and t03.book_text_type = ${bookTextType}",
            "GROUP BY",
            "t03.school_id,",
            "t03.class_type_id,",
            "t03.year_class_id",
            "<when test='isDetails!=null and isDetails!=&apos;&apos; '>",
                "<when test='isDetails == 1'>",
                    ",t03.teacher_id ",
                "</when>",
            "</when>",
            "</script>"})
    public abstract List<BookSchoolCountRs> getNotOrderSchoolCountList(
            @Param("yearPart") Integer yearPart,
            @Param("quarterNum") Integer quarterNum,
            @Param("schoolId") Long schoolId,
            @Param("subjectsId") String subjectsId,
            @Param("classTypeId") String classTypeId,
            @Param("lessonType") Integer lessonType,
            @Param("isDetails") Integer isDetails,
            @Param("teacherId") Long teacherId,
            @Param("yearClassId") Integer yearClassId,
            @Param("bookTextId") Long bookTextId,
            @Param("bookTextType") Integer bookTextType,
            @Param("subscribeType") Integer subscribeType,
            @Param("lessonNum") Integer lessonNum,
            @Param("beforeLessonNum") Integer beforeLessonNum,
            @Param("rade") Integer rade
    );


    @Select({"<script>",
            "<when test='subscribeType == 1 '>",
                "WITH subscribe_student_tb AS (",
                    "SELECT * FROM (",
                        "SELECT ",
                        "t01.id,",
                        "t01.student_name,",
                        "t01.student_id,",
                        "t01.class_type_id,",
                        "t01.year_class_id,",
                        "t01.school_id,",
                        "t03.rade,",
                        "t02.subjects_id,",
                        "lowest_shutnum,",
                        "IFNULL( t02.right_num, 0 ) AS right_num,",
                        "IFNULL( t02.amend_num, 0 ) AS amend_num,",
                        "CASE WHEN lowest_shutnum &gt; 0 THEN ROUND( IFNULL( right_num / lowest_shutnum, 0 ) * 100, 2 ) ELSE 0 END AS rate ",
                        "FROM math_db.per_student_account AS t01",
                        "INNER JOIN (",
                            "SELECT * FROM math_db.cha_plan",
                            "WHERE",
                            "year_part = ${yearPart}",
                            "AND quarter_num = ${quarterNum}",
                            "AND task_category_id = 1 ",
                            "AND deleted = 0 ",
                            "AND lesson_num = ${beforeLessonNum}",
                            "AND is_first = 1 ",
                        ") AS t02 ON t01.student_id = t02.student_id ",
                    "AND t01.class_type_id = t02.class_type_id ",
                    "AND t01.year_class_id = t02.year_class_id ",
                    "LEFT JOIN xmy_erp_db.book_accuracy_set AS t03 ON t03.class_type_id = t01.class_type_id ",
                    "AND t03.subjects_id = t02.subjects_id ",
                    ") AS t ",
                "WHERE rate &lt; rade )",
            "</when>",

            "SELECT",
            "t01.*,",
            "getSchoolName(t01.school_id) as school_name,",
            "getCourseTypeName(t01.class_type_id) class_type_name,",
            "getDictName(t01.year_class_id, 14) as year_class_name,",
            "getSubjectsName(t01.subjects_id) as subjects_name,",
            "count( t01.id ) AS count ",
            "FROM",
            "book_student AS t01 ",
            "<when test='subscribeType == 1 '>",
                "INNER JOIN subscribe_student_tb AS t02 ON t02.student_id = t01.student_id AND t01.subjects_id = t02.subjects_id AND t01.class_type_id = t02.class_type_id ",
            "</when>",
            "WHERE t01.`status` = 1 ",
            "AND t01.year_part = ${yearPart}",
            "AND t01.quarter_num = ${quarterNum}",
            "<when test='bookTextId!=null and bookTextId!=&apos;&apos; '>",
            "and t01.book_text_id IN ( ${bookTextId} )",
            "</when>",
            "AND t01.book_text_type = ${bookTextType}",
            "<when test='subscribeType == 1 '>",
                "AND t02.id IS NOT NULL ",
                "AND t01.lesson_num = ${lessonNum}",
            "</when>",
            "GROUP BY",
            "t01.subjects_id,",
            "t01.class_type_id",
            "</script>"})
    public abstract List<BookClassTypeCountRs> getNotOrderSchoolCountForClassType(@Param("yearPart") Integer yearPart,
                                                                                  @Param("quarterNum") Integer quarterNum,
                                                                                  @Param("subjectsId") String subjectsId,
                                                                                  @Param("classTypeId") String classTypeId,
                                                                                  @Param("lessonType") Integer lessonType,
                                                                                  @Param("bookTextId") Long bookTextId,
                                                                                  @Param("bookTextType") Integer bookTextType,
                                                                                  @Param("lessonNum") Integer lessonNum,
                                                                                  @Param("subscribeType") Integer subscribeType,
                                                                                  @Param("beforeLessonNum") Integer beforeLessonNum,
                                                                                  @Param("rade") Integer rade
    );

   /* @Select({"<script>",
            "SELECT",
            "t01.*,",
            "getSchoolName(t01.school_id) as school_name,",
            "getCourseTypeName(t01.class_type_id) class_type_name,",
            "getDictName(t01.year_class_id, 14) as year_class_name,",
            "getSubjectsName(t01.subjects_id) as subjects_name,",
            "count( t01.id ) AS count ",
            "FROM",
            "book_student AS t01 ",
            "WHERE t01.`status` = 1 ",
            "AND t01.year_part = ${yearPart}",
            "AND t01.quarter_num = ${quarterNum}",
            "<when test='bookTextId!=null and bookTextId!=&apos;&apos; '>",
                "and t01.book_text_id IN ( ${bookTextId} )",
            "</when>",
            "(",
            "SELECT",
            "t1.id,",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.lesson_type,",
            "t1.year_part,",
            "t1.quarter_num,",
            "t2.year_class_id,",
            "t2.course_type_id AS class_type_id ",
            "FROM",
            "student_grade AS t1",
            "INNER JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and t2.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "and t2.course_type_id = ${classTypeId}",
            "</when>",
            "AND t1.`status` = 1 ",
            "AND t1.reading_status IN ( 0, 1 ) ",
            "AND t1.lesson_type = ${lessonType} ",
            "GROUP BY",
            "student_id,",
            "subjects_id ",
            ") AS t01",
            "LEFT JOIN book_student AS t03 ON t01.year_part = t03.year_part ",
            "AND t01.quarter_num = t03.quarter_num ",
            "AND t01.student_id = t03.student_id ",
            "AND t01.subjects_id = t03.subjects_id ",
            "AND t01.lesson_type = t03.lesson_type ",
            "AND t01.class_type_id = t03.class_type_id ",
            "AND t01.year_class_id = t03.year_class_id ",
            "and t03.book_text_id IN ( ${bookTextId} )",
            //1学生用书，2教师用书
            "and t03.book_text_type = ${bookTextType} ",
            "WHERE t03.`status` = 1 ",
            "GROUP BY",
            "t01.subjects_id,",
            "t01.class_type_id",
            "</script>"})
    public abstract List<BookClassTypeCountRs> getNotOrderSchoolCountForClassType(@Param("yearPart") Integer yearPart,
                                                                                  @Param("quarterNum") Integer quarterNum,
                                                                                  @Param("subjectsId") String subjectsId,
                                                                                  @Param("classTypeId") String classTypeId,
                                                                                  @Param("lessonType") Integer lessonType,
                                                                                  @Param("bookTextId") Long bookTextId,
                                                                                  @Param("bookTextType") Integer bookTextType
    );*/


    /**
     * 获取取消订购列表
     */
    @Select({"<script>",
            "SELECT",
            "t01.*,",
            "getSchoolName(t01.school_id) as school_name,",
            "getCourseTypeName(t01.class_type_id) class_type_name,",
            "getDictName(t01.year_class_id, 14) as year_class_name,",
            "getSubjectsName(t01.subjects_id) as subjects_name,",
            "<when test='bookTextType == 2 '>",
                "t02.`name` as teacher_name,",
            "</when>",
            "count( t01.id ) AS count ",
            "FROM book_exclude_subscription AS t01 ",
            "<when test='bookTextType == 2 '>",
                "INNER JOIN staff AS t02 ON t01.teacher_id = t02.id ",
            "</when>",
            "WHERE",
            "t01.deleted = 0",
            "AND t01.year_part = ${yearPart}",
            "AND t01.quarter_num = ${quarterNum}",
            "AND t01.lesson_type = ${lessonType}",
            "AND t01.book_text_id IN ( ${bookTextId} )",
            "AND t01.book_text_type =  ${bookTextType} ",
            "AND t01.school_id in (${schoolIdStr}) ",
            "AND t01.lesson_num = ${lessonNum}",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                "AND t01.teacher_id = ${teacherId} ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "AND t01.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                "AND t01.class_type_id = ${classTypeId}",
            "</when>",
            "<when test='yearClassId!=null'>",// 避免幼儿园无法查询到
                "AND t01.year_class_id = ${yearClassId}",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "AND t01.school_id = ${schoolId} ",
            "</when>",
            "<when test='bookTextType == 2 '>",
                "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                    "AND t02.`name` LIKE '%${teacherName}%' ",
                "</when>",
            "</when>",
            "GROUP BY",
            "t01.subjects_id,",
            "t01.class_type_id",
            "<if test='type != 2'>",
                ",t01.school_id ",
            "</if>",
            "<if test='isDetails == 1'>",
                ",t01.teacher_id ",
            "</if>",
            "<if test='isDetails == 2'>",
                ",t01.student_id ",
            "</if>",
            "<if test='isDetails == 3'>",
                ",t01.year_class_id ",
            "</if>",
            "</script>"})
    public abstract IPage<BookSchoolRs> getCancelOrderList(IPage<?> page,
                                                           @Param("yearPart") Integer yearPart,
                                                           @Param("quarterNum") Integer quarterNum,
                                                           @Param("subjectsId") String subjectsId,
                                                           @Param("classTypeId") String classTypeId,
                                                           @Param("lessonType") Integer lessonType,
                                                           @Param("schoolIdStr") String schoolIdStr,
                                                           @Param("bookTextId") Long bookTextId,
                                                           @Param("bookTextType") Integer bookTextType,
                                                           @Param("isDetails") Integer isDetails,
                                                           @Param("teacherId") Long teacherId,
                                                           @Param("schoolId") Long schoolId,
                                                           @Param("yearClassId") Integer yearClassId,
                                                           @Param("teacherName") String teacherName,
                                                           @Param("studentName") String studentName,
                                                           @Param("gradeId") Long gradeId,
                                                           @Param("lessonNum") Integer lessonNum,
                                                           @Param("type") Integer type
    );

    /**
     * 获取取消订购合计
     */
    @Select({"<script>",
            "SELECT",
            "t01.*,",
            "getSchoolName(t01.school_id) as school_name,",
            "getCourseTypeName(t01.class_type_id) class_type_name,",
            "getDictName(t01.year_class_id, 14) as year_class_name,",
            "getSubjectsName(t01.subjects_id) as subjects_name,",
            "count( t01.id ) AS count ",
            "FROM book_exclude_subscription AS t01 ",
            "WHERE",
            "t01.deleted = 0",
            "AND t01.year_part = ${yearPart}",
            "AND t01.quarter_num = ${quarterNum}",
            "AND t01.lesson_type = ${lessonType}",
            "AND t01.book_text_id IN ( ${bookTextId} )",
            "AND t01.book_text_type =  ${bookTextType} ",
            "AND t01.school_id in (${schoolIdStr}) ",
            "AND t01.lesson_num = ${lessonNum}",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "AND t01.subjects_id = ${subjectsId}",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                "AND t01.class_type_id = ${classTypeId}",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "AND t01.school_id = ${schoolId} ",
            "</when>",
            "<if test='isDetails == 1'>",
                "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                    "AND t01.teacher_id = ${teacherId} ",
                "</when>",
            "</if>",
            "GROUP BY",
            "t01.school_id,",
            "t01.subjects_id,",
            "t01.class_type_id, ",
            "t01.year_class_id ",
            "<if test='isDetails == 1'>",
                ",t01.teacher_id ",
            "</if>",
            "</script>"})
    public abstract List<BookSchoolCountRs> getCancelOrderCountList(
            @Param("yearPart") Integer yearPart,
            @Param("quarterNum") Integer quarterNum,
            @Param("subjectsId") String subjectsId,
            @Param("classTypeId") String classTypeId,
            @Param("lessonType") Integer lessonType,
            @Param("schoolIdStr") String schoolIdStr,
            @Param("bookTextId") Long bookTextId,
            @Param("bookTextType") Integer bookTextType,
            @Param("isDetails") Integer isDetails,
            @Param("schoolId") Long schoolId,
            @Param("teacherId") Long teacherId,
            @Param("yearClassId") Integer yearClassId,
            @Param("lessonNum") Integer lessonNum
    );


}
